SELECT RACT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID,
LOOK1.MEANING TRANSACTION_CLASS,
DECODE(RACT.COMPLETE_FLAG,
'Y', :YES_MEANING,
:NO_MEANING) COMPLETE,
DECODE(RACTT.POST_TO_GL,
'Y', :YES_MEANING,
:NO_MEANING) POSTABLE,
LOOK5.MEANING CREDIT_METHOD_FOR_RULES,
LOOK6.MEANING CREDIT_METHOD_FOR_INSTALLMENTS,
RACT.TRX_NUMBER INVOICE_NUMBER,
RACT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID,
RAC_BILL.ACCOUNT_NUMBER BILL_TO_CUSTOMER_NUMBER,
SUBSTRB(RAC_BILL_PARTY.PARTY_NAME,1,50) BILL_TO_CUSTOMER_NAME,
RAAD_BILL_LOC.ADDRESS1 BILL_TO_ADDRESS1,
RAAD_BILL_LOC.ADDRESS2 BILL_TO_ADDRESS2,
RAAD_BILL_LOC.ADDRESS3 BILL_TO_ADDRESS3,
RAAD_BILL_LOC.ADDRESS4 BILL_TO_ADDRESS4,
RAAD_BILL_LOC.CITY BILL_TO_CITY,
NVL(RAAD_BILL_LOC.PROVINCE,
RAAD_BILL_LOC.STATE) BILL_TO_STATE,
TERR_BILL.TERRITORY_SHORT_NAME BILL_TO_COUNTRY,
RAAD_BILL_LOC.POSTAL_CODE BILL_TO_POSTAL_CODE,
SUBSTRB(RACO_BILL_PARTY.PERSON_LAST_NAME,1,50)||' '||
SUBSTRB(RACO_BILL_PARTY.PERSON_FIRST_NAME,1,40) BILL_TO_CONTACT_NAME,
RACT.SHIP_TO_CUSTOMER_ID SHIP_TO_CUSTOMER_ID,
RAC_SHIP.ACCOUNT_NUMBER SHIP_TO_CUSTOMER_NUMBER,
SUBSTRB(RAC_SHIP_PARTY.PARTY_NAME,1,50) SHIP_TO_CUSTOMER_NAME,
RAAD_SHIP_LOC.ADDRESS1 SHIP_TO_ADDRESS1,
RAAD_SHIP_LOC.ADDRESS2 SHIP_TO_ADDRESS2,
RAAD_SHIP_LOC.ADDRESS3 SHIP_TO_ADDRESS3,
RAAD_SHIP_LOC.ADDRESS4 SHIP_TO_ADDRESS4,
RAAD_SHIP_LOC.CITY SHIP_TO_CITY,
NVL(RAAD_SHIP_LOC.PROVINCE,
RAAD_SHIP_LOC.STATE ) SHIP_TO_STATE,
TERR_SHIP.TERRITORY_SHORT_NAME SHIP_TO_COUNTRY,
RAAD_SHIP_LOC.POSTAL_CODE SHIP_TO_POSTAL_CODE,
SUBSTRB(RACO_SHIP_PARTY.PERSON_LAST_NAME,1,50)||' '||
SUBSTRB(RACO_SHIP_PARTY.PERSON_FIRST_NAME,1,40) SHIP_TO_CONTACT_NAME,
SUBSTRB(RAC_SOLD_PARTY.PARTY_NAME,1,50) SOLD_TO_CUSTOMER_NAME,
RAC_SOLD.ACCOUNT_NUMBER SOLD_TO_CUSTOMER_NUMBER,
RACT.SOLD_TO_CUSTOMER_ID SOLD_TO_CUSTOMER_ID,
RACT.REMIT_TO_ADDRESS_ID REMIT_TO_ADDRESS_ID,
RAAD_REMIT_LOC.ADDRESS1 REMIT_TO_ADDRESS1,
RAAD_REMIT_LOC.ADDRESS2 REMIT_TO_ADDRESS2,
RAAD_REMIT_LOC.ADDRESS3 REMIT_TO_ADDRESS3,
RAAD_REMIT_LOC.ADDRESS4 REMIT_TO_ADDRESS4,
RAAD_REMIT_LOC.CITY REMIT_TO_CITY,
RAAD_REMIT_LOC.STATE REMIT_TO_STATE,
RAAD_REMIT_LOC.POSTAL_CODE REMIT_TO_POSTAL_CODE,
TERR_REMIT.TERRITORY_SHORT_NAME REMIT_TO_COUNTRY,
RACT.INTERFACE_HEADER_CONTEXT FLEX_CONTEXT,
RACT.TRX_DATE INVOICE_DATE,
LOOK3.MEANING INVOICE_STATUS,
GL_DIST_DATE.GL_DATE GL_DATE,
GL_DIST_DATE.AMOUNT CREDITED_AMOUNT,
ARPS.DUE_DATE DUE_DATE,
RACT.START_DATE_COMMITMENT START_DATE,
RACT.END_DATE_COMMITMENT END_DATE,
RACT.INVOICE_CURRENCY_CODE CURRENCY_tr,
RACT.EXCHANGE_DATE EXCHANGE_DATE,
RACT.EXCHANGE_RATE EXCHANGE_RATE,
RACT.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE,
DECODE(GL_DIST_DATE.ACCOUNT_SET_FLAG,
'Y', '', null) RECEIVABLES_ACCOUNT,
RACTT.TYPE TRANSACTION_TYPE_CODE,
RACTT.NAME TRANSACTION_TYPE_NAME,
RABS.NAME BATCH_SOURCE_NAME,
SOA.NAME AGREEMENT_NAME,
RAT.NAME TERM_NAME,
RAS.NAME PRIMARY_SALESPERSON,
null TERRITORY,
RACT.PURCHASE_ORDER PO_NUMBER,
RACT.PURCHASE_ORDER_REVISION PO_REVISION,
RACT.PURCHASE_ORDER_DATE PO_DATE,
RACT.INITIAL_CUSTOMER_TRX_ID COMMITMENT_NUM,
RACT_COMM.TRX_NUMBER COMM_NUMBER,
LOOK2.MEANING PRINTING_OPTION,
RACT.INTERNAL_NOTES SPECIAL_INSTRUCTIONS,
DECODE(RACTT.ACCOUNTING_AFFECT_FLAG,
'Y', :YES_MEANING, :NO_MEANING) OPEN_RECEIVABLE,
RACT.COMMENTS COMMENTS,
RACT.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER,
RACT.PREVIOUS_CUSTOMER_TRX_ID REL_RACT,
RAR.NAME INVOICING_RULE,
ARRM.NAME RECEIPT_METHOD,
decode(apba.instrument_type,'BANKACCOUNT',bk.bank_Account_name,'CREDITCARD',apba.card_issuer_name,'DEBITCARD',apba.card_issuer_name,null) CUSTOMER_BANK_ACCOUNT,
decode(apba.instrument_type,'BANKACCOUNT',apba.account_number,'CREDITCARD',apba.card_number,'DEBITCARD',apba.card_number,null) CUSTOMER_BANK_NUM,
RACT1.TRX_NUMBER CROSS_REF_NUMBER,
RACT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID,
RACT.SHIP_VIA SHIP_VIA,
ORGF.DESCRIPTION FREIGHT_CARRIER,
RACT.FOB_POINT FOB_POINT,
LOOK4.MEANING FOB_POINT_MEANING,
RACT.WAYBILL_NUMBER WAYBILL_NUMBER,
RACT.SHIP_DATE_ACTUAL SHIP_DATE,
&lp_query_show_bill cons_bill_number,
AR_ARXTDR_XMLP_PKG.bill_to_address5formula(RAAD_BILL_LOC.CITY, NVL ( RAAD_BILL_LOC.PROVINCE , RAAD_BILL_LOC.STATE ), RAAD_BILL_LOC.POSTAL_CODE, TERR_BILL.TERRITORY_SHORT_NAME) BILL_TO_ADDRESS5,
AR_ARXTDR_XMLP_PKG.ship_to_address5formula(RAAD_SHIP_LOC.CITY, NVL ( RAAD_SHIP_LOC.PROVINCE , RAAD_SHIP_LOC.STATE ), RAAD_SHIP_LOC.POSTAL_CODE, TERR_SHIP.TERRITORY_SHORT_NAME) SHIP_TO_ADDRESS5,
--AR_ARXTDR_XMLP_PKG.d_sold_toformula(RAC_SOLD.ACCOUNT_NUMBER) D_SOLD_TO,
AR_ARXTDR_XMLP_PKG.d_sold_toformula(RAC_SOLD.ACCOUNT_NUMBER,SUBSTRB(RAC_SOLD_PARTY.PARTY_NAME,1,50)) D_SOLD_TO,
AR_ARXTDR_XMLP_PKG.d_remit_toformula(RAAD_REMIT_LOC.ADDRESS1, RAAD_REMIT_LOC.ADDRESS2, RAAD_REMIT_LOC.ADDRESS3, RAAD_REMIT_LOC.ADDRESS4, RAAD_REMIT_LOC.CITY, RAAD_REMIT_LOC.STATE, RAAD_REMIT_LOC.POSTAL_CODE, TERR_REMIT.TERRITORY_SHORT_NAME) D_REMIT_TO,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_territory', 'AR', 'CT#', 101, NULL, RATT.TERRITORY_ID, 'ALL', 'Y', 'VALUE') D_TERRITORY,
fnd_flex_xml_publisher_apis.process_kff_combination_1('d_receivables_account', 'SQLGL', 'GL#', glcc_rcvbl.CHART_OF_ACCOUNTS_ID, NULL, glcc_rcvbl.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') D_RECEIVABLES_ACCOUNT,
AR_ARXTDR_XMLP_PKG.d_locationformula() D_LOCATION,
AR_ARXTDR_XMLP_PKG.tr_inv_amountformula(:TR_LN_EXTD_AMOUNT, :TR_TX_EXTD_AMOUNT, :TR_FR_EXTD_AMOUNT) TR_INV_AMOUNT,
AR_ARXTDR_XMLP_PKG.trx_transaction_flexformula(RACT.CUSTOMER_TRX_ID) TRX_TRANSACTION_FLEX
FROM AR_LOOKUPS LOOK1,
AR_LOOKUPS LOOK2,
AR_LOOKUPS LOOK3,
AR_LOOKUPS LOOK4,
AR_LOOKUPS LOOK5,
AR_LOOKUPS LOOK6,
RA_CUST_TRX_TYPES RACTT,
HZ_CUST_ACCOUNTS RAC_BILL,
HZ_PARTIES RAC_BILL_PARTY,
HZ_CUST_SITE_USES RASU_BILL,
HZ_CUST_ACCT_SITES RAAD_BILL,
HZ_PARTY_SITES RAAD_BILL_PS,
HZ_LOCATIONS RAAD_BILL_LOC,
FND_TERRITORIES_VL TERR_BILL,
HZ_CUST_ACCOUNT_ROLES RACO_BILL,
HZ_PARTIES RACO_BILL_PARTY,
HZ_RELATIONSHIPS RACO_BILL_REL,
HZ_CUST_ACCOUNTS RAC_SHIP,
HZ_PARTIES RAC_SHIP_PARTY,
HZ_CUST_SITE_USES RASU_SHIP,
HZ_CUST_ACCT_SITES RAAD_SHIP,
HZ_PARTY_SITES RAAD_SHIP_PS,
HZ_LOCATIONS RAAD_SHIP_LOC,
FND_TERRITORIES_VL TERR_SHIP,
HZ_CUST_ACCOUNT_ROLES RACO_SHIP,
HZ_PARTIES RACO_SHIP_PARTY,
HZ_RELATIONSHIPS RACO_SHIP_REL,
HZ_CUST_ACCOUNTS RAC_SOLD,
HZ_PARTIES RAC_SOLD_PARTY,
HZ_CUST_ACCT_SITES RAAD_REMIT,
HZ_PARTY_SITES RAAD_REMIT_PS,
HZ_LOCATIONS RAAD_REMIT_LOC,
FND_TERRITORIES_VL TERR_REMIT,
ar_xla_ctlgd_lines_v GL_DIST_DATE,
AR_PAYMENT_SCHEDULES ARPS,
&lp_table_show_bill
GL_CODE_COMBINATIONS GLCC_RCVBL,
RA_BATCH_SOURCES RABS,
SO_AGREEMENTS SOA,
RA_TERMS RAT,
RA_SALESREPS RAS,
RA_TERRITORIES RATT,
RA_RULES RAR,
AR_RECEIPT_METHODS ARRM,
RA_CUSTOMER_TRX RACT,
RA_CUSTOMER_TRX RACT1,
RA_CUSTOMER_TRX RACT_COMM,
ORG_FREIGHT ORGF,
IBY_TRXN_EXTENSIONS_V extn,
IBY_FNDCPT_PAYER_ASSGN_INSTR_V apba,
iby_ext_bank_accounts_v bk
WHERE LOOK1.LOOKUP_TYPE(+) = 'INV/CM'
AND LOOK1.LOOKUP_CODE(+) = RACTT.TYPE
AND LOOK2.LOOKUP_TYPE(+) = 'INVOICE_PRINT_OPTIONS'
AND LOOK2.LOOKUP_CODE(+) = RACT.PRINTING_OPTION
AND LOOK3.LOOKUP_TYPE(+) = 'INVOICE_TRX_STATUS'
AND LOOK3.LOOKUP_CODE(+) = RACT.STATUS_TRX
AND LOOK4.LOOKUP_TYPE(+) = 'FOB'
AND LOOK4.LOOKUP_CODE(+) = RACT.FOB_POINT
AND LOOK5.LOOKUP_TYPE(+) = 'CREDIT_METHOD_FOR_RULES'
AND LOOK5.LOOKUP_CODE(+) = RACT.CREDIT_METHOD_FOR_RULES
AND LOOK6.LOOKUP_TYPE(+) = 'CREDIT_METHOD_FOR_INSTALLMENTS'
AND LOOK6.LOOKUP_CODE(+) = RACT.CREDIT_METHOD_FOR_INSTALLMENTS
AND RACTT.CUST_TRX_TYPE_ID = RACT.CUST_TRX_TYPE_ID
AND RAC_BILL.CUST_ACCOUNT_ID(+) = RACT.BILL_TO_CUSTOMER_ID
AND RAC_BILL.PARTY_ID = RAC_BILL_PARTY.PARTY_ID(+)
AND RAAD_BILL.CUST_ACCT_SITE_ID(+) = RASU_BILL.CUST_ACCT_SITE_ID
AND RAAD_BILL.PARTY_SITE_ID = RAAD_BILL_PS.PARTY_SITE_ID(+)
AND RAAD_BILL_LOC.LOCATION_ID(+) = RAAD_BILL_PS.LOCATION_ID
AND RAAD_BILL_LOC.COUNTRY = TERR_BILL.TERRITORY_CODE(+)
AND RASU_BILL.SITE_USE_ID(+) = RACT.BILL_TO_SITE_USE_ID
AND RACO_BILL.CUST_ACCOUNT_ROLE_ID(+) = RACT.BILL_TO_CONTACT_ID
AND RACO_BILL.PARTY_ID = RACO_BILL_REL.PARTY_ID(+)
AND RACO_BILL_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_BILL_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_BILL_REL.DIRECTIONAL_FLAG(+) = 'F'
AND RACO_BILL.ROLE_TYPE(+) = 'CONTACT'
AND RACO_BILL_REL.SUBJECT_ID = RACO_BILL_PARTY.PARTY_ID(+)
AND RAC_SHIP.CUST_ACCOUNT_ID(+) = RACT.SHIP_TO_CUSTOMER_ID
AND RAC_SHIP.PARTY_ID = RAC_SHIP_PARTY.PARTY_ID(+)
AND RAAD_SHIP.CUST_ACCT_SITE_ID(+) = RASU_SHIP.CUST_ACCT_SITE_ID
AND RAAD_SHIP.PARTY_SITE_ID = RAAD_SHIP_PS.PARTY_SITE_ID(+)
AND RAAD_SHIP_LOC.LOCATION_ID(+) = RAAD_SHIP_PS.LOCATION_ID
AND RAAD_SHIP_LOC.COUNTRY = TERR_SHIP.TERRITORY_CODE(+)
AND RASU_SHIP.SITE_USE_ID(+) = RACT.SHIP_TO_SITE_USE_ID
AND RACO_SHIP.CUST_ACCOUNT_ROLE_ID(+) = RACT.SHIP_TO_CONTACT_ID
AND RACO_SHIP.PARTY_ID = RACO_SHIP_REL.PARTY_ID(+)
AND RACO_SHIP.ROLE_TYPE(+) = 'CONTACT'
AND RACO_SHIP_REL.SUBJECT_ID = RACO_SHIP_PARTY.PARTY_ID(+)
AND RACO_SHIP_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_SHIP_REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND RACO_SHIP_REL.DIRECTIONAL_FLAG(+) = 'F'
AND RAC_SOLD.CUST_ACCOUNT_ID(+) = RACT.SOLD_TO_CUSTOMER_ID
AND RAC_SOLD.PARTY_ID = RAC_SOLD_PARTY.PARTY_ID(+)
AND RAAD_REMIT.CUST_ACCT_SITE_ID(+) = RACT.REMIT_TO_ADDRESS_ID
AND RAAD_REMIT.PARTY_SITE_ID = RAAD_REMIT_PS.PARTY_SITE_ID(+)
AND RAAD_REMIT_LOC.LOCATION_ID(+) = RAAD_REMIT_PS.LOCATION_ID
AND RAAD_REMIT_LOC.COUNTRY = TERR_REMIT.TERRITORY_CODE(+)
AND GL_DIST_DATE.CUSTOMER_TRX_ID(+) = RACT.CUSTOMER_TRX_ID
AND GL_DIST_DATE.ACCOUNT_CLASS(+) = 'REC'
AND GL_DIST_DATE.LATEST_REC_FLAG = 'Y'
AND ARPS.CUSTOMER_TRX_ID(+) = RACT.CUSTOMER_TRX_ID
AND ARPS.TERMS_SEQUENCE_NUMBER(+) = 1
AND GLCC_RCVBL.CODE_COMBINATION_ID(+) = GL_DIST_DATE.CODE_COMBINATION_ID
AND RABS.BATCH_SOURCE_ID(+) = RACT.BATCH_SOURCE_ID
AND SOA.AGREEMENT_ID(+) = RACT.AGREEMENT_ID
AND RAT.TERM_ID(+) = RACT.TERM_ID
AND RAS.SALESREP_ID(+) = RACT.PRIMARY_SALESREP_ID
AND RATT.TERRITORY_ID(+) = RACT.TERRITORY_ID
AND RAR.RULE_ID(+) = RACT.INVOICING_RULE_ID
AND ARRM.RECEIPT_METHOD_ID(+) = RACT.RECEIPT_METHOD_ID
AND extn.trxn_extension_id(+) = ract.payment_trxn_extension_id
and extn.instrument_id = apba.instrument_id(+)
AND extn.instr_assignment_id = apba.instr_assignment_id(+)
and bk.bank_account_id(+) = decode(apba.instrument_type,'BANKACCOUNT',apba.instrument_id,null)
AND RACT1.CUSTOMER_TRX_ID(+) = RACT.RELATED_CUSTOMER_TRX_ID
AND RACT_COMM.CUSTOMER_TRX_ID(+) = RACT.INITIAL_CUSTOMER_TRX_ID
AND (RACT.TRX_NUMBER BETWEEN :P_INVOICE_NUM_LOW AND :P_INVOICE_NUM_HIGH)
/*AND ((RACTT.TYPE = :P_TRANSACTION_TYPE) OR
(:P_TRANSACTION_TYPE = 'ALL'))*/
AND ((RACTT.TYPE = :P_TRANSACTION_TYPE_DUMMY) OR
(:P_TRANSACTION_TYPE_DUMMY = 'ALL'))
AND ORGF.FREIGHT_CODE(+) = RACT.SHIP_VIA
AND ORGF.ORGANIZATION_ID(+) = :ITEM_FLEX_STRUCTURE
&lp_where_show_bill
ORDER BY
RACT.TRX_NUMBER |