Display system date / report generated date as below
<?xdoxslt:sysdate('DD-MON-YYYY HH24:MI‘)?>
<?xdoxslt:sysdate('DD-MM-YYYY HH24:MI‘)?>
<?xdoxslt:sysdate('DD-MON-YYYY‘)?>
Thursday, February 7, 2013
Wednesday, February 6, 2013
Insert Serial Number Directly to RTF Design (XML Publisher)
One blizzard problem comes during report development is with the serial number .
When you give two or more grouping in your query , serial number make hell lot of problem in EXCEL output. To resolve this problem a simple xml code has to be inserted in the RTF design.
1. Insert your design and then copy paste any of your field to the front.
2. Then double click your first field and then change the code as
<xsl:value-of select="position()"/>
When you give two or more grouping in your query , serial number make hell lot of problem in EXCEL output. To resolve this problem a simple xml code has to be inserted in the RTF design.
1. Insert your design and then copy paste any of your field to the front.
2. Then double click your first field and then change the code as
<xsl:value-of select="position()"/>
SNO
|
Date
|
Trans Type
|
Credit
|
F SNO
|
ACCOUNTED_DATE
|
CATEGORY
|
CREDIT E
|
3. Then you are done, here i will share my output format
| SNO | Date | Trans Type | Credit |
| 1 | 12-Jun-12 | Misc Receipts | 40 |
| 2 | 13-Jun-12 | Receiving | 20 |
| 3 | 13-Jun-12 | Receiving India | |
| 4 | 18-Jun-12 | Misc Receipts |
CRM Module - AMC Expiry Query
SELECT
rownum SR_NO, OPERATING_UNIT, CONTRACT_NUMBER, CUSTOMER_NAME,LOCATION,
BILL_TO,SHIP_TO,SITE_INCHARGE_NAME, CONTACT_DETAILS,
MACHINE_MODEL, SERIAL_NUMBER, START_DATE, END_DATE,
PAYMENT_TERMS, SALES_PERSON_NAME,AMOUNT, CLE_ID
FROM (SELECT XX.SR_NO, XX.OPERATING_UNIT, XX.CONTRACT_NUMBER, XX.CUSTOMER_NAME,XX.CITY LOCATION,
XX.BILL_TO, XX.SHIP_TO, XX.SITE_INCHARGE_NAME, XX.CONTACT_DETAILS,
XX.MACHINE_MODEL, XX.SERIAL_NUMBER, XX.START_DATE, XX.END_DATE,
XX.PAYMENT_TERMS, XX.SALES_PERSON_NAME,XX.AMOUNT, XX.CLE_ID
FROM (SELECT DISTINCT rownum SR_NO, X.OPERATING_UNIT, X.CONTRACT_NUMBER, X.CUSTOMER_NAME,X.CITY, X.PAYMENT_TERM_ID,
(SELECT DISTINCT HL.ADDRESS1
|| HL.ADDRESS2
|| HL.ADDRESS3
|| HL.CITY
|| HL.STATE
FROM OKC_K_HEADERS_ALL_B OKHAB,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL
WHERE OKHAB.CONTRACT_NUMBER = X.CONTRACT_NUMBER
AND HCSUA.SITE_USE_ID =
OKHAB.SHIP_TO_SITE_USE_ID
AND HCASA.CUST_ACCT_SITE_ID =
HCSUA.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID) BILL_TO,
X.SHIP_TO, X.SITE_INCHARGE_NAME, X.CONTACT_DETAILS,
X.MACHINE_MODEL, X.SERIAL_NUMBER, X.START_DATE, X.END_DATE,
(SELECT RT.NAME FROM RA_TERMS RT ,okc_k_headers_all_b a
WHERE A.PAYMENT_TERM_ID=RT.TERM_ID
AND a.ID = X.ID) PAYMENT_TERMS,
(select LAST_NAME ||' '|| FIRST_NAME from PER_ALL_PEOPLE_F where person_id =
( select PERSON_ID from jtf_rs_salesreps where salesrep_id = NVL(X.OBJECT1_ID1,salesrep_id)))
SALES_PERSON_NAME,
X.AMOUNT, X.CLE_ID
FROM (SELECT DISTINCT HR.NAME AS OPERATING_UNIT,
OKHAB.CONTRACT_NUMBER AS CONTRACT_NUMBER,I.CLE_ID,
OKHAB.PAYMENT_TERM_ID,OK.OBJECT1_ID1,
OLDV.CUSTOMER_ACCOUNT_NAME CUSTOMER_NAME,CA.CITY,
OKHAB.ID,
( CA.ADDRESS1
|| CA.ADDRESS2
|| CA.ADDRESS3
|| CA.CITY
) SHIP_TO,
OKLB.ATTRIBUTE1 SITE_INCHARGE_NAME,
OKLB.ATTRIBUTE2 CONTACT_DETAILS,
MTL.DESCRIPTION MACHINE_MODEL,
CII.SERIAL_NUMBER SERIAL_NUMBER,
-- OAL.START_DATE, OAL.END_DATE,
(TO_CHAR (OAL.START_DATE,'DD-MON-RRRR ')) START_DATE,
OAL.END_DATE END_DATE,
ROUND(OKLB.PRICE_NEGOTIATED) AS AMOUNT
FROM OKC_K_HEADERS_ALL_B OKHAB,
OKC_K_LINES_B OKLB,
HR_OPERATING_UNITS HR,
OKS_LINE_DETAILS_V OLDV,
OKS_AUTH_LINES_V OAL,
OKC_K_ITEMS I,
CSI_ITEM_INSTANCES CII,
MTL_SYSTEM_ITEMS_B MTL,
CSI_HZPTY_ADDRESSES_V CA,
OKC_CONTACTS OK,
HZ_PARTY_SITE_USES HZP
WHERE HR.ORGANIZATION_ID = OKHAB.AUTHORING_ORG_ID
AND OKLB.DNZ_CHR_ID = OKHAB.ID
AND OLDV.CONTRACT_ID = OKHAB.ID
AND OAL.CLE_ID = OKLB.CLE_ID
AND I.CLE_ID = OKLB.ID
AND CII.INSTANCE_ID = I.OBJECT1_ID1
AND CII.INVENTORY_ITEM_ID =
MTL.INVENTORY_ITEM_ID
AND OKHAB.INV_ORGANIZATION_ID =
MTL.ORGANIZATION_ID
AND CII.LOCATION_ID = CA.LOCATION
AND CA.LOCATION = HZP.PARTY_SITE_ID(+)
AND SITE_USE_TYPE(+) = 'INSTALL_AT'
AND HZP.STATUS(+) = 'A'
AND CA.STATUS = 'A'
and ok.DNZ_CHR_ID = NVL(OKHAB.ID , OK.dnz_chr_id)
AND ok.SALES_GROUP_ID IS NOT NULL
AND OKHAB.CONTRACT_NUMBER =
NVL (:CONTRACT_NUMBER,
OKHAB.CONTRACT_NUMBER
)
AND OKHAB.ORG_ID = NVL (:P_ORG_ID, OKHAB.ORG_ID)
AND TRUNC (OAL.END_DATE)
BETWEEN NVL (TO_DATE (:P_START_DATE),
TRUNC (OAL.END_DATE)
)
AND NVL (TO_DATE (:P_END_DATE),
TRUNC (SYSDATE)
)
) X
WHERE X.CUSTOMER_NAME IS NOT NULL
ORDER BY X.CLE_ID ASC
) XX WHERE XX.SALES_PERSON_NAME IS NOT NULL
UNION ALL
SELECT XX.SR_NO, XX.OPERATING_UNIT, XX.CONTRACT_NUMBER, XX.CUSTOMER_NAME, XX.CITY LOCATION,
XX.BILL_TO, XX.SHIP_TO, XX.SITE_INCHARGE_NAME, XX.CONTACT_DETAILS,
XX.MACHINE_MODEL, NULL SERIAL_NUMBER, XX.START_DATE, XX.END_DATE,
XX.PAYMENT_TERMS, XX.SALES_PERSON_NAME,XX.AMOUNT, XX.CLE_ID FROM (
SELECT DISTINCT rownum SR_NO ,X.OPERATING_UNIT, X.CONTRACT_NUMBER, X.CUSTOMER_NAME,/*X.CITY ,*/ X.PAYMENT_TERM_ID,
(SELECT DISTINCT HL.ADDRESS1
|| HL.ADDRESS2
|| HL.ADDRESS3
|| HL.CITY
|| HL.STATE
FROM OKC_K_HEADERS_ALL_B OKHAB,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HL
WHERE OKHAB.CONTRACT_NUMBER = X.CONTRACT_NUMBER
AND HCSUA.SITE_USE_ID =
OKHAB.SHIP_TO_SITE_USE_ID
AND HCASA.CUST_ACCT_SITE_ID =
HCSUA.CUST_ACCT_SITE_ID
AND HPS.PARTY_SITE_ID = HCASA.PARTY_SITE_ID
AND HL.LOCATION_ID = HPS.LOCATION_ID) BILL_TO,
( SELECT DISTINCT HLA.CITY
FROM OKC_K_HEADERS_ALL_B OKHAB,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HLA
WHERE OKHAB.CONTRACT_NUMBER =X.CONTRACT_NUMBER
and hcsua.SITE_USE_ID=okhab.BILL_TO_SITE_USE_ID
and hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID
and hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
and HLA.LOCATION_ID = hps.LOCATION_ID) CITY,
( SELECT DISTINCT HLA.ADDRESS1
|| HLA.ADDRESS2
|| HLA.ADDRESS3
|| HLA.CITY
|| HLA.STATE
FROM OKC_K_HEADERS_ALL_B OKHAB,
HZ_CUST_SITE_USES_ALL HCSUA,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_PARTY_SITES HPS,
HZ_LOCATIONS HLA
WHERE OKHAB.CONTRACT_NUMBER =X.CONTRACT_NUMBER
and hcsua.SITE_USE_ID=okhab.BILL_TO_SITE_USE_ID
and hcasa.CUST_ACCT_SITE_ID = hcsua.CUST_ACCT_SITE_ID
and hps.PARTY_SITE_ID = hcasa.PARTY_SITE_ID
and HLA.LOCATION_ID = hps.LOCATION_ID) SHIP_TO, X.SITE_INCHARGE_NAME, X.CONTACT_DETAILS,
X.MACHINE_MODEL, X.START_DATE, X.END_DATE,
(SELECT RT.NAME FROM RA_TERMS RT ,okc_k_headers_all_b a
WHERE A.PAYMENT_TERM_ID=RT.TERM_ID
AND a.ID = X.ID) PAYMENT_TERMS,
(select LAST_NAME ||' '|| FIRST_NAME from PER_ALL_PEOPLE_F where person_id =
( select PERSON_ID from jtf_rs_salesreps where salesrep_id = NVL(X.OBJECT1_ID1,salesrep_id)))
SALES_PERSON_NAME,
X.AMOUNT, X.CLE_ID FROM (
SELECT DISTINCT HR.NAME AS OPERATING_UNIT,
OKHAB.CONTRACT_NUMBER AS CONTRACT_NUMBER,I.CLE_ID,
OKHAB.PAYMENT_TERM_ID,OK.OBJECT1_ID1,
OLDV.CUSTOMER_ACCOUNT_NAME CUSTOMER_NAME,
OKHAB.ID,
OKLB.ATTRIBUTE1 SITE_INCHARGE_NAME,
OKLB.ATTRIBUTE2 CONTACT_DETAILS,
MTL.DESCRIPTION MACHINE_MODEL,
NULL SERIAL_NUMBER,
(TO_CHAR (OAL.START_DATE,'DD-MON-RRRR ') )START_DATE,
OAL.END_DATE END_DATE,
OKLB.PRICE_NEGOTIATED AS AMOUNT
FROM OKC_K_HEADERS_ALL_B OKHAB,
OKC_K_LINES_B OKLB,
HR_OPERATING_UNITS HR,
OKS_LINE_DETAILS_V OLDV,
OKS_AUTH_LINES_V OAL,
OKC_K_ITEMS I,
-- CSI_ITEM_INSTANCES CII,
MTL_SYSTEM_ITEMS_B MTL,
-- CSI_HZPTY_ADDRESSES_V CA,
OKC_CONTACTS OK,
HZ_PARTY_SITE_USES HZP
WHERE HR.ORGANIZATION_ID = OKHAB.AUTHORING_ORG_ID
AND OKLB.DNZ_CHR_ID = OKHAB.ID
AND OLDV.CONTRACT_ID = OKHAB.ID
AND OAL.CLE_ID = OKLB.CLE_ID
AND I.CLE_ID = OKLB.ID
AND I.OBJECT1_ID2 = TO_CHAR(MTL.ORGANIZATION_ID)
AND I.OBJECT1_ID1 = TO_CHAR(MTL.INVENTORY_ITEM_ID)
AND OKHAB.INV_ORGANIZATION_ID =
MTL.ORGANIZATION_ID
AND MTL.ITEM_TYPE IS NOT NULL
and ok.DNZ_CHR_ID = NVL(OKHAB.ID , OK.dnz_chr_id)
AND ok.SALES_GROUP_ID IS NOT NULL
AND OKHAB.CONTRACT_NUMBER =
NVL (:CONTRACT_NUMBER,
OKHAB.CONTRACT_NUMBER
)
AND OKHAB.ORG_ID = NVL (:P_ORG_ID, OKHAB.ORG_ID)
AND TRUNC (OAL.END_DATE)
BETWEEN NVL (TO_DATE (:P_START_DATE),
TRUNC (OAL.END_DATE)
)
AND NVL (TO_DATE (:P_END_DATE),
TRUNC (SYSDATE)
)
)X
WHERE X.CUSTOMER_NAME IS NOT NULL
ORDER BY X.CLE_ID ASC
)XX
)
ORDER BY SR_NO
CRM Module - AMC Billing Advice and Invoice Summary Query
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
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
Tuesday, February 5, 2013
Query to find Concurrent Programs & Responsibility
Query to find the concurrent programs and responsibility.
SELECT frt.responsibility_name, user_concurrent_program_name,
frg.request_group_name,
frg.description, fcp.concurrent_program_name
FROM fnd_request_groups frg
,fnd_request_group_units frgu
,fnd_concurrent_programs fcp
,fnd_concurrent_programs_tl fcpt
,fnd_responsibility_tl frt
,fnd_responsibility frs
WHERE frgu.unit_application_id = fcp.application_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frg.request_group_id = frgu.request_group_id
AND frg.application_id = frgu.application_id
AND fcpt.source_lang = USERENV('LANG')
AND fcp.application_id = fcpt.application_id
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND frs.application_id = frt.application_id
AND frs.responsibility_id = frt.responsibility_id
AND frt.source_lang = USERENV('LANG')
AND frs.request_group_id = frg.request_group_id
AND frs.application_id = frg.application_id
-- AND fcp.concurrent_program_name = 'XX_CUS_CFORM'
AND fcpt.user_concurrent_program_name LIKE '%Delivery Challan With Excise Invoice%'
Subscribe to:
Posts (Atom)