Oracle_Apps

Join bw JAI_CMN_RG_23AC_II_TRXS and rcv_transactions

SELECT DISTINCT a.rma_reference, a.organization_id --,a.*
FROM
apps.JAI_CMN_RG_23AC_II_TRXS jip,
rcv_transactions a
WHERE jip.organization_id = a.organization_id
--AND jip.receipt_id = a.transaction_id
AND jip.RECEIPT_REF = a.transaction_id
AND TRUNC(a.CREATION_DATE) <= '30-JUN-2013'
AND a.rma_reference IS NOT NULL;

RA_ADDRESS_ALL Replacement in R12 -- Oracle Apps

SELECT DISTINCT HCA.CUST_ACCOUNT_ID CUSTOMER_ID,
HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
HP.PARTY_NAME CUSTOMER_NAME,
HCSU.CUST_ACCT_SITE_ID ADDRESS_ID,
HL.ADDRESS1,
HL.ADDRESS2,
HL.ADDRESS3,
HL.ADDRESS4,
HL.CITY,
HL.POSTAL_CODE,
UPPER(HL.STATE) STATE,
DECODE(NVL(HCSA.SHIP_TO_FLAG,'N'),'P','P','Y','Y','N') SHIP_TO_FLAG,
DECODE(NVL(HCSA.BILL_TO_FLAG,'N'),'P','Y','Y','Y','N') BILL_TO_FLAG
FROM APPS.HZ_PARTIES HP,
APPS.HZ_PARTY_SITES HPS,
APPS.HZ_LOCATIONS HL,
APPS.HZ_CUST_ACCOUNTS_ALL HCA,
APPS.HZ_CUST_ACCT_SITES_ALL HCSA,
APPS.HZ_CUST_SITE_USES_ALL HCSU
WHERE HP.PARTY_ID = HPS.PARTY_ID
AND HPS.LOCATION_ID = HL.LOCATION_ID
AND HP.PARTY_ID = HCA.PARTY_ID
AND HCSA.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND HCSU.CUST_ACCT_SITE_ID = HCSA.CUST_ACCT_SITE_ID
AND HCA.CUST_ACCOUNT_ID = HCSA.CUST_ACCOUNT_ID
--AND TO_DATE(TO_CHAR(HCSA.CREATION_DATE,'DD-MON-YYYY'),'DD-MON-YYYY') >= '28-JUN-2010'
AND (HCA.CUSTOMER_CLASS_CODE = 'WEB DISTRIBUTORS' OR HCA.SALES_CHANNEL_CODE = 'KEY WEB CUSTOMERS')
ORDER BY HL.ADDRESS1 --,HCSA.CREATION_DATE

Credit Hold Query -- Oracle Apps

SELECT
DISTINCT HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
HP.PARTY_NAME CUSTOMER_NAME,
OHA.ORDER_NUMBER,
OHA.ORDERED_DATE,
OHD.NAME HOLD_NAME,
OHD.TYPE_CODE HOLD_TYPE,
FLV.MEANING RELEASE_REASON_CODE,
FU.USER_NAME RELEASED_BY,
OHR.CREATION_DATE RELEASE_DATE
FROM
OE_ORDER_HEADERS_ALL OHA,
ONT.OE_ORDER_HOLDS_ALL OOHA,
ONT.OE_HOLD_SOURCES_ALL OHSA,
OE_HOLD_DEFINITIONS OHD,
OE_HOLD_RELEASES OHR,
FND_USER FU,
FND_LOOKUP_VALUES FLV,
HZ_CUST_ACCOUNTS HCA,
HZ_PARTIES HP
WHERE trunc(ORDERED_DATE) BETWEEN TO_DATE(:PFROM,'DD-MON-YYYY') AND TO_DATE(:TDATE,'DD-MON-YYYY')
AND OHA.HEADER_ID=OOHA.HEADER_ID
AND OOHA.HOLD_SOURCE_ID=OHSA.HOLD_SOURCE_ID
AND OHSA.HOLD_ID=OHD.HOLD_ID
AND OOHA.HOLD_RELEASE_ID=OHR.HOLD_RELEASE_ID
AND OHR.CREATED_BY=FU.USER_ID
AND OHR.RELEASE_REASON_CODE=FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE='RELEASE_REASON'
and oha.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND HCA.PARTY_ID=HP.PARTY_ID
ORDER BY OHA.ORDERED_DATE

To get the employee details by using PER_PEOPLE_F table

SELECT
PPF.PERSON_ID USERID,
PPF.EMPLOYEE_NUMBER EMPLOYEE_NUM,
PPF.LAST_NAME USER_NAME,
--rtrim(rtrim(EMAIL_ADDRESS,'signodeindia.com'),'@')ALIAS_NAME,
SUBSTR(PPF.EMAIL_ADDRESS,1,INSTR(PPF.EMAIL_ADDRESS,'@',1)-1) ALIAS_NAME,
PPF.EMAIL_ADDRESS,
PPF.LAST_NAME FULL_NAME,
--PPF.EFFECTIVE_START_DATE JOING_DATE,
TO_CHAR(PPF.DATE_OF_BIRTH,'DD-MON-YYYY')DATE_OF_BIRTH,
PAV.DEFAULT_CODE_COMB_ID,
IGCV.SEGMENT2,
IGCV.SEG2_DESC,
IGCV.SEGMENT4 COST_CODE,
IGCV.SEG4_DESC COST_CODE_DESC
,PAV.LOCATION_CODE,
PAV.ADDRESS_LINE_1 ADD1,
PAV.ADDRESS_LINE_2 ADD2,
PAV.ADDRESS_LINE_3 ADD3,
PAV.TOWN_OR_CITY,
PAV.COUNTRY,
PAV.POSTAL_CODE,
PAV.TELEPHONE_NUMBER_1,
PAV.TELEPHONE_NUMBER_2,
PPF.WORK_TELEPHONE TEL
FROM
PER_PEOPLE_F PPF,
PER_ASSIGNMENTS_V7 PAV,
GL_CODE_COMBINATIONS IGCV
WHERE
PPF.PERSON_ID=PAV.PERSON_ID
AND PPF.EMPLOYEE_NUMBER=PAV.ASSIGNMENT_NUMBER
AND PAV.DEFAULT_CODE_COMB_ID=IGCV.CODE_COMBINATION_ID
--AND SUBSTR(PPF.EMAIL_ADDRESS,INSTR(PPF.EMAIL_ADDRESS,'@',1)+1) != 'ramesh.com'
order by PPF.EMPLOYEE_NUMBER

Item with categories query -- Oracle Apps

SELECT DISTINCT MSIB.ORGANIZATION_ID,OOD.ORGANIZATION_NAME,IGC.SEGMENT6 PRODUCT_CODE,IGC.SEG6_DESC PRODUCT ,MSIB.SEGMENT1 ITEM_CODE,MSIB.DESCRIPTION,MC.SEGMENT1 MAJOR_CATEGORY,MC.SEGMENT2 MINOR_CATEGORY
FROM
GL_CODE_COMBINATIONS IGC,
MTL_SYSTEM_ITEMS_B MSIB,
MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORIES MC,
ORG_ORGANIZATION_DEFINITIONS OOD
WHERE
IGC.CODE_COMBINATION_ID = MSIB.SALES_ACCOUNT
AND MSIB.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MSIB.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MIC.CATEGORY_ID = MC.CATEGORY_ID
AND MSIB.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND MSIB.ORGANIZATION_ID = 10

TO find the excisable,modavate flag items -- Oracle Apps R12

SELECT ORGANIZATION_ID, MTL.SEGMENT1 ITEM_CODE,DESCRIPTION ITEM_DESCRIPTION,MTL.PRIMARY_UOM_CODE UOM,MTL.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS,IGCC.SEGMENT6 PRODUCT_CODE,IGCC.SEG6_DESC PRODUCT,
MTL.ATTRIBUTE6 MODEL,MTL.ATTRIBUTE7 CLASSIFICATION,
SUBSTR(MTL.DESCRIPTION,1,INSTR(MTL.DESCRIPTION,' ',1,1)) PART_NO,
(SELECT F1.ATTRIBUTE_VALUE
FROM
JAI_RGM_ITEM_ATTRIB_V F1
WHERE F1.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND F1.ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND F1.ATTRIBUTE_CODE = 'EXCISABLE') EXC_NEX_FLG,
(SELECT F1.ATTRIBUTE_VALUE
FROM
JAI_RGM_ITEM_ATTRIB_V F1
WHERE F1.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND F1.ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND F1.ATTRIBUTE_CODE = 'MODVATABLE') MODVAT_FLAG,
(SELECT F1.ATTRIBUTE_VALUE
FROM JAI_RGM_ITEM_ATTRIB_V F1
WHERE F1.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID
AND F1.ORGANIZATION_ID = MTL.ORGANIZATION_ID
AND F1.ATTRIBUTE_CODE = 'TRADABLE') TRADING_FLAG
FROM MTL_SYSTEM_ITEMS_B MTL,
GL_CODE_COMBINATIONS IGCC
WHERE
MTL.SALES_ACCOUNT = IGCC.CODE_COMBINATION_ID
AND MTL.ORGANIZATION_ID = 1000
;

Item Tariff Heading -- Oracle Apps R12

SELECT distinct e.attribute_value item_tarrif
FROM apps.JAI_OM_WSH_LINES_ALL a,
OE_ORDER_LINES_ALL b,
mtl_system_items c,
jai_rgm_itm_regns d,
jai_rgm_itm_tmpl_attrs e
WHERE a.order_line_id = b.line_id
and b.flow_status_code in ('SHIPPED','FULFILLMENT','CLOSED')
and a.inventory_item_id = c.inventory_item_id
and a.organization_id = c.organization_id
and c.inventory_item_id = d.inventory_item_id
and c.organization_id = d.organization_id
AND d.rgm_item_regns_id = e.rgm_item_regns_id
AND e.attribute_code in ('ITEM TARIFF')
and a.INVENTORY_ITEM_ID=:INVENTORY_ITEM_ID
and b.header_id = :ORDER_HEADER_ID
and a.delivery_id = nvl(:DELIVERY_ID,a.delivery_id);