This is a small query for summary of AMC billing advice and invoice summary.
SELECT DISTINCT ROWNUM, BB.CREATERNAME, BB.CHECK_NUMBER, BB.PAYMENT_TYPE_CODE,
BB.PAYMENT_AMOUNT, BB.REFERENO, BB.CONTNAME, BB.CONTNO,
BB.SPL_INS, BB.PAYMENT_STATUS,
BB.SALESREP_NAME, BB.PTC,
BB.TERM_NAME, BB.MACH_MOD, BB.ORDER_NO,
BB.ORDERED_DATE,
BB.PRODUCT, BB.DESCR, BB.QTY, BB.P_NAME, BB.BILL_ADDRS1,
BB.BILL_ADDRS2, BB.BILL_ADDRS3, BB.SHIP_ADDRS1,
BB.SHIP_ADDRS2, BB.SHIP_ADDRS3, BB.LOCATION_CUST, BB.AG_NO,
BB.C_NO, BB.MAC_MOD, BB.TT_NAME, BB.ORG_ID,
BB.TRANSACTION_TYPE_ID, BB.ORDER_NUMBER, BB.INVENTORY_ITEM_ID,
BB.ORDERED_ITEM, BB.TRX_DATE, BB.TRX_NUMBER,
BB.ACCTD_AMOUNT_DUE_REMAINING, BB.L_D
FROM (SELECT DISTINCT B.CREATERNAME, B.CHECK_NUMBER,
B.PAYMENT_TYPE_CODE, B.PAYMENT_AMOUNT,
B.REFERENO, B.CONTNAME, B.CONTNO, B.SPL_INS,
B.PAYMENT_STATUS,
B.SALESREP_NAME, B.PTC,
B.TERM_NAME, B.MACH_MOD, B.ORDER_NO,
B.ORDERED_DATE,
B.PRODUCT, B.DESCR, B.QTY,
B.P_NAME, B.BILL_ADDRS1, B.BILL_ADDRS2,
B.BILL_ADDRS3, B.SHIP_ADDRS1, B.SHIP_ADDRS2,
B.SHIP_ADDRS3, B.LOCATION_CUST, B.AG_NO,
(SELECT DISTINCT OKHAB.CONTRACT_NUMBER
FROM OKC_K_HEADERS_ALL_B OKHAB
WHERE OKHAB.CONTRACT_NUMBER =
B.AG_NO)
C_NO,
B.MAC_MOD, B.TT_NAME, B.ORG_ID,
B.TRANSACTION_TYPE_ID, B.ORDER_NUMBER,
B.INVENTORY_ITEM_ID, B.ORDERED_ITEM,
B.TRX_DATE, B.TRX_NUMBER,
B.ACCTD_AMOUNT_DUE_REMAINING,
(SELECT DISTINCT MSIF.LONG_DESCRIPTION
FROM MTL_SYSTEM_ITEMS_FVL MSIF,
MTL_SYSTEM_ITEMS_B MSI
WHERE MSIF.INVENTORY_ITEM_ID =
MSI.INVENTORY_ITEM_ID
AND MSIF.ORGANIZATION_ID =
MSI.ORGANIZATION_ID
AND MSIF.INVENTORY_ITEM_ID =
B.INVENTORY_ITEM_ID)
L_D,
B.LINE_NUMBER
FROM (SELECT DISTINCT FU.USER_NAME CREATERNAME,
OOHA.CHECK_NUMBER,
OOHA.PAYMENT_TYPE_CODE,
OOHA.PAYMENT_AMOUNT,
OOLA.ATTRIBUTE1 REFERENO,
OOLA.ATTRIBUTE2 CONTNAME,
OOLA.ATTRIBUTE3 CONTNO,
OOLA.ATTRIBUTE4 SPL_INS,
OOLA.ATTRIBUTE6
PAYMENT_STATUS,
PAPF.LAST_NAME
|| ' '
|| PAPF.FIRST_NAME
SALESREP_NAME,
OEP.NAME PTC,
RAT.NAME TERM_NAME,
OOLA.ATTRIBUTE5 MACH_MOD,
OOHA.ORDER_NUMBER ORDER_NO,
OOHA.ORDERED_DATE,
OOLA.LINE_NUMBER SNO,
OOLA.ORDERED_ITEM PRODUCT,
(SELECT DISTINCT DESCRIPTION
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1 =
OOLA.ORDERED_ITEM
AND ORGANIZATION_ID =
OOHA.SHIP_FROM_ORG_ID)
DESCR,
OOLA.ORDERED_QUANTITY QTY,
HP.PARTY_NAME P_NAME,
BILL_LOC.ADDRESS1
BILL_ADDRS1,
BILL_LOC.ADDRESS2
BILL_ADDRS2,
BILL_LOC.CITY
|| ' ,'
|| BILL_LOC.STATE
|| ' - '
|| BILL_LOC.POSTAL_CODE
BILL_ADDRS3,
SHIP_LOC.ADDRESS1
SHIP_ADDRS1,
SHIP_LOC.ADDRESS2
SHIP_ADDRS2,
SHIP_LOC.CITY
|| ' ,'
|| SHIP_LOC.STATE
|| ' - '
|| SHIP_LOC.POSTAL_CODE
SHIP_ADDRS3,
SHIP_LOC.CITY LOCATION_CUST,
OOLA.ATTRIBUTE8 AG_NO,
QSLHV.NAME MAC_MOD,
OTTL.NAME TT_NAME,
OOHA.ORG_ID,
OTTL.TRANSACTION_TYPE_ID,
OOHA.ORDER_NUMBER,
OOLA.INVENTORY_ITEM_ID,
OOLA.ORDERED_ITEM,
RCT.TRX_DATE,
RCT.TRX_NUMBER,
OOLA.LINE_NUMBER,
APS.ACCTD_AMOUNT_DUE_REMAINING
FROM
--CSI_ITEM_INSTANCES CII ,OKC_K_HEADERS_ALL_B OKHAB,OKC_K_LINES_B OKLB,OKC_K_ITEMS I,
OE_ORDER_HEADERS_ALL OOHA,
OE_ORDER_LINES_ALL OOLA,
HR_LOCATIONS HR,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP,
HZ_CUST_SITE_USES_ALL SHIP_SU,
HZ_CUST_SITE_USES_ALL BILL_SU,
HZ_CUST_ACCT_SITES_ALL SHIP_CAS,
HZ_CUST_ACCT_SITES_ALL BILL_CAS,
HZ_PARTY_SITES SHIP_PS,
HZ_PARTY_SITES BILL_PS,
HZ_LOCATIONS SHIP_LOC,
HZ_LOCATIONS BILL_LOC,
QP_SECU_LIST_HEADERS_V QSLHV,
OE_TRANSACTION_TYPES_TL OTTL,
RA_TERMS RAT,
CS_INCIDENTS_B_SEC CSIBS,
FND_USER FS,
OE_PAYMENT_TYPES_TL OEP,
RA_SALESREPS_ALL RASA,
PER_ALL_PEOPLE_F PAPF,
FND_USER FU,
JAI_OM_OE_SO_LINES JOOS,
RA_CUSTOMER_TRX_ALL RCT,
AR_PAYMENT_SCHEDULES_ALL APS
WHERE OOHA.ORDER_NUMBER =
NVL (:XX_ONO,
OOHA.ORDER_NUMBER
)
AND OOHA.ORG_ID =
NVL (:XX_ORG,
OOHA.ORG_ID)
AND OOLA.HEADER_ID =
OOHA.HEADER_ID
AND OTTL.TRANSACTION_TYPE_ID =
NVL
(:XX_TTN,
OTTL.TRANSACTION_TYPE_ID
)
AND OTTL.TRANSACTION_TYPE_ID =
OOHA.ORDER_TYPE_ID
AND OOHA.SHIP_FROM_ORG_ID =
HR.INVENTORY_ORGANIZATION_ID
AND OOHA.SOLD_TO_ORG_ID =
HCA.CUST_ACCOUNT_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND OOHA.SHIP_TO_ORG_ID = SHIP_SU.SITE_USE_ID(+)
AND OOHA.INVOICE_TO_ORG_ID = BILL_SU.SITE_USE_ID(+)
AND SHIP_SU.CUST_ACCT_SITE_ID =
SHIP_CAS.CUST_ACCT_SITE_ID(+)
AND BILL_SU.CUST_ACCT_SITE_ID =
BILL_CAS.CUST_ACCT_SITE_ID(+)
AND SHIP_CAS.PARTY_SITE_ID = SHIP_PS.PARTY_SITE_ID(+)
AND BILL_CAS.PARTY_SITE_ID = BILL_PS.PARTY_SITE_ID(+)
AND SHIP_LOC.LOCATION_ID(+) =
SHIP_PS.LOCATION_ID
AND BILL_LOC.LOCATION_ID(+) =
BILL_PS.LOCATION_ID
AND OOLA.PRICE_LIST_ID = QSLHV.LIST_HEADER_ID(+)
AND OOHA.PAYMENT_TERM_ID(+) =
RAT.TERM_ID
AND OOHA.ORIG_SYS_DOCUMENT_REF =
CSIBS.INCIDENT_NUMBER(+)
AND OOHA.CREATED_BY = FS.USER_ID(+)
AND OEP.PAYMENT_TYPE_CODE(+) =
OOHA.PAYMENT_TYPE_CODE
AND OOLA.ITEM_TYPE_CODE <>
'SERVICE'
AND RASA.SALESREP_ID(+) =
OOHA.SALESREP_ID
AND PAPF.PERSON_ID(+) =
RASA.PERSON_ID
AND OEP.ORG_ID(+) = OOHA.ORG_ID
AND FU.USER_ID = OOHA.CREATED_BY
AND JOOS.HEADER_ID =
OOHA.HEADER_ID
AND RCT.TRX_NUMBER =
APS.TRX_NUMBER
AND RCT.CT_REFERENCE =
TO_CHAR
(OOHA.ORDER_NUMBER)) B
ORDER BY B.LINE_NUMBER, B.ORDER_NUMBER ASC) BB
ORDER BY ROWNUM, BB.ORDER_NUMBER
No comments:
Post a Comment