THURSDAY, 14 JUNE 2012

po report query
SELECT
O.NAME"OPERATING_UNIT_NAME",
PH.SEGMENT1"PO_REV_NUM",
PH.PO_HEADER_ID,
PH.TYPE_LOOKUP_CODE"TYPE",
PH.CURRENCY_CODE,
PH.AUTHORIZATION_STATUS"STATUS",
PV.VENDOR_SITE_CODE,
V.VENDOR_NAME"SUPPLIER_NAME",
LOC1.LOCATION_CODE"SHIP_TO_LOC",
LOC2.LOCATION_CODE"BILL_TO_LOC",
(PL1.QUANTITY*PL1.UNIT_PRICE)"TOTAL",
LINESINFORMATION
PL1.LINE_NUM,
PL1.PURCHASE_BASIS"TYPE",
I.SEGMENT1"ITEM",
PL1.ITEM_REVISION,
C.SEGMENT1||C.SEGMENT1"CATEGORY",
PL1.ITEM_DESCRIPTION,
PL1.QUANTITY,
PL.UNIT_MEAS_LOOKUP_CODE,
PL1.UNIT_PRICE,
PL.NEED_BY_DATE,
PL.SHIPMENT_TYPE,
PL.SHIPMENT_NUM,
M.ORGANIZATION_CODE"SHP_ORG_CODE",
LOC1.LOCATION_CODE"SHIPMENT_LOC",
PL.UNIT_MEAS_LOOKUP_CODE"SHP_UOM",
PL.QUANTITY"SHP_QUANTITY",
(PL.QUANTITY*PL.PRICE_OVERRIDE)"SHP_AMOUNT",
D.DISTRIBUTION_NUM,
D.DESTINATION_TYPE_CODE,
GL.CONCATENATED_SEGMENTS,
D.QUANTITY_ORDERED"DIS_QUANTITY",
P.FULL_NAME"REQUESTERNAME",
I.INVENTORY_ITEM_ID,
C.CATEGORY_ID
FROM
PO_HEADERS
HR_ORGANIZATION_UNITSO,
po_headers_allPH,
PO_VENDORSV,
PO_VENDOR_SITES_ALLPV,
HR_LOCATIONS_ALL_TLLOC1,
HR_LOCATIONS_ALL_TLLOC2,
PO_LINES
po_line_locations_allPL,
PO_LINES_ALLPL1,
MTL_SYSTEM_ITEMS_BI,
MTL_CATEGORIES_BC,
MTL_PARAMETERSM,
PO_DISTRIBUTIONS_ALLD,
GL_CODE_COMBINATIONS_KFVGL,
PER_ALL_PEOPLE_FP
WHEREPH.VENDOR_SITE_ID=PV.VENDOR_SITE_ID
ANDPH.SHIP_TO_LOCATION_ID=PV.SHIP_TO_LOCATION_ID
ANDPH.VENDOR_ID=V.VENDOR_ID
ANDLOC1.LOCATION_ID=PH.SHIP_TO_LOCATION_ID
ANDLOC2.LOCATION_ID=PV.BILL_TO_LOCATION_ID
ANDPH.PO_HEADER_ID=PL1.PO_HEADER_ID
LINES
ANDPL1.ITEM_ID=I.INVENTORY_ITEM_ID
ANDPL.PO_HEADER_ID=PL1.PO_HEADER_ID
ANDPL.PO_LINE_ID=PL1.PO_LINE_ID
ANDPL1.CATEGORY_ID=C.CATEGORY_ID
ORG_CODE
ANDM.ORGANIZATION_ID=PL.SHIP_TO_ORGANIZATION_ID
ANDO.ORGANIZATION_ID=M.ORGANIZATION_ID
ANDC.ORGANIZATION_ID=M.ORGANIZATION_ID
ANDO.ORGANIZATION_ID=PH.ORG_ID
DISTRIBTION
ANDD.PO_HEADER_ID=PL.PO_HEADER_ID
ANDD.PO_LINE_ID=PL.PO_LINE_ID
ANDD.LINE_LOCATION_ID=PL.LINE_LOCATION_ID
ANDGL.CODE_COMBINATION_ID=D.CODE_COMBINATION_ID
ANDD.DELIVER_TO_PERSON_ID=P.PERSON_ID
ANDPH.SEGMENT1like'PO_NUM%'
ANDO.NAMELIKE'Vision%';
Posted by Elangovan Ragavan at 11:33 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction, REPORT_QUERY

MONDAY, 16 APRIL 2012

Query to find the Flexfields and valuesets

SELECT A.ID_FLEX_STRUCTURE_CODE,
B.ID_FLEX_CODE,
E.LANGUAGE,
D.FLEX_VALUE,
E.DESCRIPTION,
E.FLEX_VALUE_MEANING,
B.FLEX_VALUE_SET_ID,
B.APPLICATION_COLUMN_NAME,
B.SEGMENT_NAME,
C.FLEX_VALUE_SET_NAME,
 D.FLEX_VALUE_ID
FROM APPS.fnd_id_flex_structures A,
APPS.fnd_id_flex_segments B,
APPS.fnd_flex_value_sets C,
APPS.fnd_flex_values D,
APPS.fnd_flex_values_tl E
WHERE A.ID_FLEX_NUM=B.ID_FLEX_NUM
AND B.FLEX_VALUE_SET_ID=C.FLEX_VALUE_SET_ID
AND C.FLEX_VALUE_SET_ID=D.FLEX_VALUE_SET_ID
AND D.FLEX_VALUE_ID=E.FLEX_VALUE_ID
AND A.APPLICATION_ID IN (101,201)
AND E.LANGUAGE='US'
ORDER BY A.ID_FLEX_STRUCTURE_CODE
Posted by Elangovan Ragavan at 18:05 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

MONDAY, 20 FEBRUARY 2012

multi- org securiy query

select psp.SECURITY_PROFILE_NAME,
psp.SECURITY_PROFILE_ID,
hou.NAME,
hou.ORGANIZATION_ID
from PER_SECURITY_PROFILES psp,
PER_SECURITY_ORGANIZATIONS pso,
HR_OPERATING_UNITS hou
where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
and pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;
Posted by Elangovan Ragavan at 18:13 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

FRIDAY, 27 JANUARY 2012

gl_drill_down_to_ap_invoice_liablity_account
select distinct
AIA.invoice_id,
-- AIA.ORG_ID,
aia.DOC_SEQUENCE_VALUE,
aia.invoice_num,
-- aia.gl_date,
aia.INVOICE_AMOUNT,
jl.ACCOUNTED_CR,
jl.ACCOUNTED_DR,
-- jl.JE_LINE_NUM,
jb.JE_BATCH_ID ,
jl.JE_HEADER_ID,
Gcc.SEGMENT1
|| '.'
|| Gcc.SEGMENT2
|| '.'
 || Gcc.SEGMENT3 -- -1545500 3835344 adv
|| '.'
|| Gcc.SEGMENT4
|| '.'
|| Gcc.SEGMENT5
|| '.'
|| Gcc.SEGMENT6
|| '.'
|| Gcc.SEGMENT7
"CODE_COMBINATION"
from gl_je_headers jh,gl_je_lines jl,gl_code_combinations Gcc,
gl_import_references GIr,gl_je_batches jb ,ap_invoices_all AIA
where jl.je_header_id = jh.je_header_id
and AIA.ACCTS_PAY_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
and AIA.INVOICE_ID = gir.REFERENCE_2
and aia.DOC_SEQUENCE_VALUE = gir.SUBLEDGER_DOC_SEQUENCE_VALUE
AND jl.code_combination_id = Gcc.code_combination_id
AND jh.je_header_id = GIr.je_header_id
AND jl.je_line_num = GIr.je_line_num
AND jl.je_header_id = GIr.je_header_id
AND jb.je_batch_id = jh.je_batch_id
and gir.JE_BATCH_ID = jb.JE_BATCH_ID
AND JH.JE_SOURCE = 'Payables'
AND JH.je_category = 'Purchase Invoices'
--and JH.PERIOD_NAME = 'APR-11'
AND GCC.SEGMENT1 = '41'
AND GCC.SEGMENT3 = '373100'
--and jh.je_header_id =2404071
and jl.SET_OF_BOOKS_ID = 5
Posted by Elangovan Ragavan at 18:49 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

THURSDAY, 12 JANUARY 2012

PO_RECEIPT_WITH_TAX
select *
from ja_in_receipt_tax_lines jtl, rcv_shipment_lines rcl,
rcv_shipment_headers rch
where rch.receipt_num like '200604211'
and jtl.tax_type in ('Service')
and rcl.shipment_line_id = jtl.shipment_line_id
and rcl.shipment_header_id = rch.shipment_header_id

-----------------------------------------------------------------------

SELECT /*+INDEX (RSH RCV_SHIPMENT_HEADERS_U1) INDEX (GCCK

GL_CODE_COMBINATIONS_U1)*/
rsh.receipt_num, rt.transaction_type, NULL account_type,
gcck.concatenated_segments, rrsl.accounted_cr, rrsl.accounted_dr,
rrsl.currency_code, ood.organization_code organization_code,
 rrsl.accounting_date gl_date
FROM rcv_receiving_sub_ledger rrsl,
rcv_transactions rt,
rcv_shipment_headers rsh,
gl_code_combinations_kfv gcck,
org_organization_definitions ood
WHERE rrsl.rcv_transaction_id = rt.transaction_id(+)
AND rt.shipment_header_id = rsh.shipment_header_id(+)
AND rrsl.code_combination_id = gcck.code_combination_id(+)
AND rsh.organization_id = ood.organization_id(+)
AND rsh.receipt_num in
('200500915','200502972','200600332','200703754','200805644','200900262')
UNION
SELECT jirjb.receipt_num, jirjb.acct_nature, jirjb.acct_type,
gcck.concatenated_segments, jirjb.entered_cr, jirjb.entered_dr,
jirjb.currency_code, ood.organization_code organization_code,
jirjb.transaction_date gl_date
FROM ja_in_rcv_journals_b jirjb,
gl_code_combinations_kfv gcck,
org_organization_definitions ood
WHERE jirjb.code_combination_id = gcck.code_combination_id(+)
AND jirjb.organization_code = ood.organization_code(+)
AND jirjb.receipt_num in
('200500915','200502972','200600332','200703754','200805644','200900262')

select * from JAI_RCV_JOURNAL_ENTRIES ----> This table is R12

ja_in_rcv_journals_b ----- > This is 11i table

Posted by Elangovan Ragavan at 23:38 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

FRIDAY, 25 NOVEMBER 2011

invoice_validatad or never_validated
exec

SELECT

invoice_num,approval_status_lookup_code,ORG_IDFROMap_invoices_vWHEREinvoice
_numin('RECEIPT/426014245/2730024',MO_GLOBAL.INIT('SQLAP');
Posted by Elangovan Ragavan at 01:09 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

TUESDAY, 1 NOVEMBER 2011

ITEM_LAST_PO_QRY
SELECT p.unit_price,
P.LIST_PRICE_PER_UNIT,
(SELECT PRIMARY_UNIT_OF_MEASURE
FROM mtl_system_items_b
 WHERE inventory_item_id = p.item_id
AND SUBSTR (SEGMENT1, 1, 1) = :P_ITEM_CODE
AND organization_id = 1206)
UOM,
b.item_id,
(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = p.item_id
AND SUBSTR (SEGMENT1, 1, 1) = :P_ITEM_CODE
AND organization_id = 1206)
item_code,
b.po_date
FROM po_lines_all p,
( SELECT item_id, MAX (creation_date) po_date
FROM po_lines_All
WHERE item_id IN
(SELECT inventory_item_id
FROM mtl_system_items_b msi
WHERE SUBSTR (MSI.SEGMENT1, 1, 2) = :P_ITEM_CODE
AND organization_id = 1206)
AND TRUNC (Creation_date) <= '31-MAR-2011'
GROUP BY item_id) b
WHERE p.item_id = b.item_id AND p.creation_date = b.po_date
order by b.item_id
Posted by Elangovan Ragavan at 15:52 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

XX_XX_BATCH_CREATION
CREATE OR REPLACE PROCEDURE APPS.XX_XX_BATCH_CREATION (
ERRBUFF OUT VARCHAR2,
RETCODE OUT NUMBER,
P_BATCH_NUMBER IN VARCHAR2,
P_WHSE_CODE IN VARCHAR2,
P_SHIFT_CODE IN NUMBER,
P_DATE IN VARCHAR2
)
AS
P_API_VERSION NUMBER DEFAULT 1 ;
P_VALIDATION_LEVEL NUMBER DEFAULT GME_API_PUB.MAX_ERRORS ;
P_INIT_MSG_LIST BOOLEAN DEFAULT FALSE ;
P_BATCH_TYPE NUMBER DEFAULT 0 ;
X_BATCH_HEADER GME_BATCH_HEADER%ROWTYPE;
X_MESSAGE_COUNT NUMBER;
X_MESSAGE_LIST VARCHAR2 (1000);
X_RETURN_STATUS VARCHAR2 (1);
L_BATCH_HEADER GME_BATCH_HEADER%ROWTYPE;
L_MSG_INDEX_OUT NUMBER;
XX_EXCEPTION_MATERIAL_TBL GME_API_PUB.UNALLOCATED_MATERIALS_TAB;
-- DECLARING TMS VARIABLES
V_ROUTE_CODE VARCHAR2 (100);
 V_START_SHIFT_CODE NUMBER;
V_TM_ITEM_CODE VARCHAR2 (30);
V_TRIP_START_DATE DATE;
V_TM_ITEM_ID NUMBER;
V_UNLOAD_QUANTITY NUMBER;
V_ORGN_CODE VARCHAR2 (30);
V_WHSE_CODE VARCHAR2 (30);
V_ITEM_CODE VARCHAR2 (100);
V_LOT_NO NUMBER;
-- TMS VARIABLES DECLARATION ENDED
-- DECLARING VARIABLE FOR UPDATE WITH THE QUANTITY
L_MATERIAL_DETAIL GME_MATERIAL_DETAILS%ROWTYPE;
X_MATERIAL_DETAIL GME_MATERIAL_DETAILS%ROWTYPE;
L_VALUE_TAB GME_API_PUB.FIELD_VALUES_TAB;
L_INDEX NUMBER;
L_BATCH_ID GME_BATCH_HEADER.BATCH_ID%TYPE;
L_MATERIAL_DETAIL_ID GME_MATERIAL_DETAILS.MATERIAL_DETAIL_ID%TYPE;
V_PLANT_CODE VARCHAR2 (10);
V_ITEM_ID NUMBER;
V_NUM NUMBER := 2;
V_SCALE_TYPE NUMBER;
V_COST_ALLOC NUMBER;
V_CONTRIBUTE_YIELD_IND VARCHAR2 (10) := NULL;
V_ITEM_UOMS VARCHAR2 (10);
V_MATERIAL_DETAIL_ID NUMBER;
L_TRAN_ROW GME_INVENTORY_TXNS_GTMP%ROWTYPE;
LX_TRAN_ROW GME_INVENTORY_TXNS_GTMP%ROWTYPE;
LX_MATERIAL_DETAIL GME_MATERIAL_DETAILS%ROWTYPE;
LX_DEF_TRAN_ROW GME_INVENTORY_TXNS_GTMP%ROWTYPE;
L_TRANSACTIONS GME_API_PUB.INV_TRANS_REC_TAB;
L_TRANSACTION IC_TRAN_PND%ROWTYPE;
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2 (1000);
V_CO_CODE VARCHAR2 (100);
V_WHSE_NAME VARCHAR2 (1000);
V_ACTUAL_DATE DATE;
P_RECEIPE_NO VARCHAR2 (100);
P_RECEIPE_ID NUMBER;
P_RECEIPE_RULE NUMBER;
P_RECEIPE_VERSION NUMBER;
REJECTION_FLAG NUMBER := 0;
-- ENDED
--- DECLARE CURSOR TO GET THE ITEM AND THE QUANTITY DETAILS FOR WHICH
THE PRODUCTION BATCH NEEDS TO BE CREATED
CURSOR C_GET_TOTAL_QUANTITY
IS
SELECT --ROUTE_CODE, ROUTE_NAME,
START_SHIFT_CODE,
TM_ITEM_CODE,
TTH.ATTRIBUTE5 RL_SECTION, -- TTH.ATTRIBUTE9 REASON_CD,
TRUNC (TRIP_START_DATE) TRIP_START_DATE,
 TM_ITEM_ID,
SUM (NVL (TTL.NO_OF_TRIPS, 1) * UNLOAD_QUANTITY)
TOTAL_QUANTITY,
ORGN_CODE,
WHSE_CODE,
(SELECT ITEM_ID
FROM IC_ITEM_MST
WHERE ITEM_NO = TM_ITEM_CODE)
ITEM_ID
FROM TM_TRIP_HEADERS TTH,
TM_ROUTES TR,
TM_TRIP_LINES TTL,
TM_LOCATIONS TL,
XX_OPM_TM DOT
WHERE TTH.ROUTE_ID = TR.ROUTE_ID
AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
AND DOT.LOC_CODE = TL.TM_LOCATION_ID
AND START_SHIFT_CODE = P_SHIFT_CODE
AND TO_CHAR (TRUNC (TRIP_START_DATE), 'YYYYMMDD') = P_DATE
AND ORGN_TYPE = 'M'
AND WHSE_CODE = P_WHSE_CODE
GROUP BY --ROUTE_CODE,
--ROUTE_NAME,
START_SHIFT_CODE,
TM_ITEM_CODE,
TRUNC (TRIP_START_DATE),
TM_ITEM_ID,
ORGN_CODE,
WHSE_CODE,
-- TTH.ATTRIBUTE9,
TTH.ATTRIBUTE5;
-- CURSOR TO SUM THE ITEM QUANTITY FOR THE BATCH ALLOCATION
CURSOR C_INSERT_LINE_ALLOCATION (
P_ITEM_CODE VARCHAR2
)
IS
SELECT SUM (QTY) QTY, WHSE_1, REASON_CD
FROM ( SELECT NVL ( (SELECT WHSE_CODE
FROM XX_OPM_TM DOT
WHERE LOC_CODE = DEST_SUB_INVENTORY),
WHSE_CODE)
WHSE_1,
TTH.ATTRIBUTE9 REASON_CD,
SUM (NVL (TTL.NO_OF_TRIPS, 1) * UNLOAD_QUANTITY) QTY
FROM TM_TRIP_HEADERS TTH,
TM_ROUTES TR,
TM_TRIP_LINES TTL,
TM_LOCATIONS TL,
XX_OPM_TM DOT
WHERE TTH.ROUTE_ID = TR.ROUTE_ID
 AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
AND DOT.LOC_CODE = TL.TM_LOCATION_ID
AND START_SHIFT_CODE = P_SHIFT_CODE
AND TO_CHAR (TRUNC (TRIP_START_DATE), 'YYYYMMDD') =
P_DATE
AND ORGN_TYPE = 'M'
AND WHSE_CODE = P_WHSE_CODE
AND TM_ITEM_CODE = P_ITEM_CODE
GROUP BY ROUTE_CODE,
ROUTE_NAME,
START_SHIFT_CODE,
TM_ITEM_CODE,
TRIP_START_DATE,
TM_ITEM_ID,
ORGN_CODE,
WHSE_CODE,
SOURCE_SUB_INVENTORY,
TTH.ATTRIBUTE9,
TTH.ATTRIBUTE5,
DEST_SUB_INVENTORY)
GROUP BY WHSE_1, REASON_CD;
-- END OF DECLARATION
BEGIN
-- GET THE DETAILS OF PLANT CODE,WAREHOUSE CODE AND ACTUAL DATE FROM
TRUCK M SYSTEM
BEGIN
SELECT DISTINCT
DOT.ORGN_CODE,
DOT.WHSE_CODE,
TO_DATE (
TRUNC (TRIP_START_DATE)
|| ' '
|| TO_CHAR (SHIFT_START / 3600),
'dd-mon-yy hh24:mi'
)
TRIP_START_DATE
INTO V_PLANT_CODE, V_WHSE_CODE, V_ACTUAL_DATE
FROM TM_TRIP_HEADERS TTH,
TM_ROUTES TR,
TM_TRIP_LINES TTL,
TM_LOCATIONS TL,
XX_OPM_TM DOT,
IC_WHSE_MST WHS,
MR_SHDY_DTL C
WHERE TTH.ROUTE_ID = TR.ROUTE_ID
AND TTH.TM_TRANSACTION_ID = TTL.TM_TRANSACTION_ID
AND TR.SOURCE_SUB_INVENTORY = TL.TM_LOCATION_ID
AND DOT.LOC_CODE = TL.TM_LOCATION_ID
AND START_SHIFT_CODE = P_SHIFT_CODE
AND DOT.WHSE_CODE = WHS.WHSE_CODE
 AND WHS.ATTRIBUTE1 = C.SHOPDAY_NO
AND START_SHIFT_CODE = P_SHIFT_CODE
AND C.SHIFT_NO = P_SHIFT_CODE
AND TO_CHAR ( (TRIP_START_DATE), 'YYYYMMDD') = P_DATE
AND ORGN_TYPE = 'M'
AND DOT.WHSE_CODE = P_WHSE_CODE;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'error while fetching values from TMS =' || SQLERRM
);
END;
-- TO GET THE DETAIL OF THE COMPANY CODE BASED ON THE PLANT CODE
FETCHED ABOVE
BEGIN
SELECT CO_CODE
INTO V_CO_CODE
FROM SY_ORGN_MST
WHERE ORGN_CODE = V_PLANT_CODE;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'error while fetching values from CO_CODE' || SQLERRM
);
END;
-- TO GET THE NAME OF THE WAREHOUSE FROM THE WAREHOUSE CODE
BEGIN
SELECT WHSE_NAME
INTO V_WHSE_NAME
FROM IC_WHSE_MST
WHERE WHSE_CODE = V_WHSE_CODE;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'error while fetching values from WHSE_NAME' || SQLERRM
);
END;

---------------------FETCH RECEIPE INFORMATION ----------------------------

BEGIN
SELECT RECIPE_ID, RECIPE_NO, RECIPE_VERSION
INTO P_RECEIPE_ID, P_RECEIPE_NO, P_RECEIPE_VERSION
FROM GMD_RECIPES
WHERE RECIPE_VERSION = (SELECT MAX (RECIPE_VERSION)
FROM GMD_RECIPES
 WHERE ATTRIBUTE1 = P_WHSE_CODE)
AND ATTRIBUTE1 = P_WHSE_CODE;
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'receipe id and recipe no ' || P_RECEIPE_ID || ' ' || P_RECEIPE_NO
);
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'receipe id and recipe no not fetched' || SQLERRM
);
END;

---------------------------------------------------------------------
------------------FETCHING RECIPE VALIDATION RULE --------------------------------
BEGIN
SELECT RECIPE_VALIDITY_RULE_ID
INTO P_RECEIPE_RULE
FROM GMD_RECIPE_VALIDITY_RULES
WHERE RECIPE_ID = P_RECEIPE_ID;

FND_FILE.PUT_LINE (FND_FILE.LOG,
'receipe validity rule ' || P_RECEIPE_RULE);
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'receipe validity rule not fetched due to ' || SQLERRM
);
END;

-- CREATION OF BATCH
----------------------------------------------------------------------------------

L_BATCH_HEADER.PLANT_CODE := V_PLANT_CODE;
L_BATCH_HEADER.BATCH_TYPE := P_BATCH_TYPE;
L_BATCH_HEADER.BATCH_NO :=
V_WHSE_NAME || '/' || P_DATE || '/' || P_SHIFT_CODE || '10';
L_BATCH_HEADER.PLAN_START_DATE := SYSDATE;
L_BATCH_HEADER.PLAN_CMPLT_DATE := SYSDATE;
L_BATCH_HEADER.UPDATE_INVENTORY_IND := 'Y';
L_BATCH_HEADER.RECIPE_VALIDITY_RULE_ID := P_RECEIPE_RULE;
L_BATCH_HEADER.WIP_WHSE_CODE := V_WHSE_CODE;
L_BATCH_HEADER.ACTUAL_START_DATE := V_ACTUAL_DATE;
FND_GLOBAL.APPS_INITIALIZE (
USER_ID => FND_PROFILE.VALUE ('USER_ID'),
RESP_ID => FND_PROFILE.VALUE ('RESP_ID'),
RESP_APPL_ID => FND_PROFILE.VALUE ('RESP_APPL_ID')
);
GME_API_PUB.CREATE_BATCH (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_BATCH_HEADER => L_BATCH_HEADER,
X_BATCH_HEADER => X_BATCH_HEADER,
P_BATCH_SIZE => NULL,
P_BATCH_SIZE_UOM => NULL,
P_CREATION_MODE => 'RECIPE',
P_RECIPE_ID => NULL,
P_RECIPE_NO => P_RECEIPE_NO,
P_RECIPE_VERSION => P_RECEIPE_VERSION,
P_PRODUCT_NO => NULL,
P_PRODUCT_ID => NULL,
P_IGNORE_QTY_BELOW_CAP => TRUE,
P_IGNORE_SHORTAGES => TRUE,
P_USE_SHOP_CAL => 0,
P_CONTIGUITY_OVERRIDE => 0,
X_UNALLOCATED_MATERIAL => XX_EXCEPTION_MATERIAL_TBL
);
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);

----------------WRITE THE DETAILS OF THE BATCH IN THE LOG FILE ---------------------------

FND_FILE.PUT_LINE (FND_FILE.LOG, 'create_batch =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'create_batch x_batch_header.batch_id= '
|| TO_CHAR (X_BATCH_HEADER.BATCH_ID)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
SUBSTR ('x_batch_header.plant_code =' || X_BATCH_HEADER.PLANT_CODE,
1,
255)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
SUBSTR ('x_batch_header.batch_no =' || X_BATCH_HEADER.BATCH_NO, 1, 255)
);
COMMIT;
-- THE BELOW CODE WILL STORE THE ITEM ID OF REJECTION ITEM IN V_ITEM_ID
SELECT ITEM_ID
INTO V_ITEM_ID
FROM FM_MATL_DTL
WHERE FORMULA_ID IN (SELECT FORMULA_ID
 FROM GMD_RECIPES
WHERE RECIPE_NO LIKE 'M-H-2 TOP%')
AND LINE_TYPE = 1;
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;

-- THE BELOW CODE WILL FIND THE SCALE TYPE ,COST ALLOCATION,CONTRIBUTE
TOWARDS YIELD INDICATOR AND UOM ASSOCIATED
-- WITH THE REJECTION ITEM CODE.SAME SET OF VALUES WILL BE USED FOR ANY
NEW MATERIAL LINE INSERTION
BEGIN
SELECT SCALE_TYPE,
COST_ALLOC,
CONTRIBUTE_YIELD_IND,
ITEM_UM
INTO V_SCALE_TYPE,
V_COST_ALLOC,
V_CONTRIBUTE_YIELD_IND,
V_ITEM_UOMS
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID
AND LINE_TYPE = 1
AND ITEM_ID = V_ITEM_ID;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG,
'ERRROR WHILE FETCHING MATERIAL DETAILS');
END;

-- START THE UPDATE OF THE BATCH MATERIAL LINES BASED ON THE ITEM AND
QUANTITY RETURNED FROM THE TMS QUERY
FOR I IN C_GET_TOTAL_QUANTITY
LOOP
IF I.ITEM_ID = V_ITEM_ID -- I.E. IF REJECTION ITEM IS ALSO A PART OF THE TMS
QUERY
THEN
REJECTION_FLAG := 1;
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
-- IF REJECTION ITEM IS A PART OF THE TMS QUERY THEN FETCH THE MATERIAL
DETAIL ID OF THE REJECTION ITEM FROM THE
-- NEWLY CREATED BATCH TO UPDATE ITS QUANTITY
BEGIN
SELECT MATERIAL_DETAIL_ID
INTO L_MATERIAL_DETAIL_ID
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID
AND LINE_TYPE = 1
AND ITEM_ID = V_ITEM_ID;
END;
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
 L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_INDEX := 1;
L_VALUE_TAB (L_INDEX).P_VALUE := 'PLAN_QTY';
GME_API_PUB.UPDATE_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_VALUES_TAB => L_VALUE_TAB,
P_SCALE_PHANTOM => FALSE,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
-- TO DISPLAY THE RESULT OF THE ABOVE LINE UPDATE
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_message_list =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id= '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
-------------------------INSERT THE ATTRIBUTE3(RL SECTION) VALUE
-----------------------------------
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.ATTRIBUTE3 := I.RL_SECTION;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_INDEX := 1;
L_VALUE_TAB (L_INDEX).P_VALUE := 'ATTRIBUTE3';
GME_API_PUB.UPDATE_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
 P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_VALUES_TAB => L_VALUE_TAB,
P_SCALE_PHANTOM => FALSE,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
-- TO DISPLAY THE RESULT OF RL SECTION UPDATE
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_message_list =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id= '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
-- TO FETCH THE ITEM CODE AND THE CURRENT LOT NUMBER BASED ON THE
ITEM ID
BEGIN
SELECT ITEM_NO, LOT_SUFFIX
INTO V_ITEM_CODE, V_LOT_NO
FROM IC_ITEM_MST
WHERE ITEM_ID = I.ITEM_ID;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'UNABLE TO FIND ITEM');
END;

---------------INSERT INTO LINE ALLOCATION FOR THE REJECTION ITEM

FOR J IN C_INSERT_LINE_ALLOCATION (V_ITEM_CODE)
LOOP
-- /////////////////////////////////////////////////////////////
-- SET VALUES
-- /////////////////////////////////////////////////////////////
L_TRAN_ROW.DOC_ID := L_BATCH_ID;
 L_TRAN_ROW.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_TRAN_ROW.WHSE_CODE := J.WHSE_1;
L_TRAN_ROW.TRANS_QTY := J.QTY;
L_TRAN_ROW.TRANS_DATE := V_ACTUAL_DATE;
L_TRAN_ROW.REASON_CODE := J.REASON_CD;
-- /////////////////////////////////////////////////////////////

GME_API_PUB.INSERT_LINE_ALLOCATION (
P_API_VERSION => GME_API_PUB.API_VERSION,
P_VALIDATION_LEVEL => GME_API_PUB.MAX_ERRORS,
P_INIT_MSG_LIST => TRUE,
P_COMMIT => TRUE,
P_TRAN_ROW => L_TRAN_ROW,
P_LOT_NO => V_LOT_NO,
P_CREATE_LOT => TRUE,
X_MATERIAL_DETAIL => LX_MATERIAL_DETAIL,
X_TRAN_ROW => LX_TRAN_ROW,
X_DEF_TRAN_ROW => LX_DEF_TRAN_ROW,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS
);
-- /////////////////////////////////////////////////////////////
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_count for line allocation ='
|| TO_CHAR (X_MESSAGE_COUNT)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list for line allocation =' || X_MESSAGE_LIST
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_return_status for line allocation =' || X_RETURN_STATUS
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id for line allocation = '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
V_LOT_NO := V_LOT_NO + 1;
END LOOP;
COMMIT;
 -----------------INSERT END
-- IF THE ITEMS RETURNED FROM THE TRUCKING MODULE QUERY ARE NOT
REJECTION ITEM INSERT THEM IN THE NEWLY CREATED BATCH
ELSE
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;

BEGIN
SELECT MAX (MATERIAL_DETAIL_ID)
INTO L_MATERIAL_DETAIL_ID
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID AND LINE_TYPE = 1;
END;
FND_FILE.PUT_LINE (FND_FILE.LOG,
'mateiral detail id' || L_MATERIAL_DETAIL_ID);
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_MATERIAL_DETAIL.LINE_NO := V_NUM;
L_MATERIAL_DETAIL.ITEM_UM := V_ITEM_UOMS;
L_MATERIAL_DETAIL.LINE_TYPE := 1;
L_MATERIAL_DETAIL.SCALE_TYPE := V_SCALE_TYPE;
L_MATERIAL_DETAIL.COST_ALLOC := 0;
L_MATERIAL_DETAIL.CONTRIBUTE_STEP_QTY_IND := 'Y';
L_MATERIAL_DETAIL.ALLOC_IND := 1;
L_MATERIAL_DETAIL.ORIGINAL_QTY := 0.000000;
L_MATERIAL_DETAIL.ATTRIBUTE3 := I.RL_SECTION;
GME_API_PUB.INSERT_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_BATCHSTEP_NO => NULL,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'insert_material_line x_message_list =' || X_MESSAGE_LIST
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'insert_material_line x_return_status =' || X_RETURN_STATUS
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'insert_material_line x_batch_header.batch_id= '
 || TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID + 1;
L_MATERIAL_DETAIL.PLAN_QTY := I.TOTAL_QUANTITY;
L_MATERIAL_DETAIL.ITEM_ID := I.ITEM_ID;
L_MATERIAL_DETAIL.LINE_NO := V_NUM;
L_MATERIAL_DETAIL.ITEM_UM := V_ITEM_UOMS;
L_MATERIAL_DETAIL.LINE_TYPE := 1;
L_MATERIAL_DETAIL.SCALE_TYPE := V_SCALE_TYPE;
L_MATERIAL_DETAIL.COST_ALLOC := 0;
L_MATERIAL_DETAIL.CONTRIBUTE_STEP_QTY_IND := 'Y';
L_MATERIAL_DETAIL.ALLOC_IND := 1;
L_MATERIAL_DETAIL.ORIGINAL_QTY := 0.000000;
V_MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID + 1;
L_TRANSACTIONS (1).ITEM_ID := I.ITEM_ID;
L_TRANSACTIONS (1).LINE_ID := V_MATERIAL_DETAIL_ID;
L_TRANSACTIONS (1).CO_CODE := V_CO_CODE;
L_TRANSACTIONS (1).WHSE_CODE := V_WHSE_CODE;
L_TRANSACTIONS (1).LOT_ID := 0;
L_TRANSACTIONS (1).LOCATION := 'NONE';
L_TRANSACTIONS (1).DOC_ID := L_BATCH_ID;
L_TRANSACTIONS (1).DOC_TYPE := 'PROD';
L_TRANSACTIONS (1).LINE_TYPE := 1;
L_TRANSACTIONS (1).TRANS_QTY := 0;
L_TRANSACTIONS (1).TRANS_UM := V_ITEM_UOMS;
L_TRANSACTIONS (1).TRANS_DATE := V_ACTUAL_DATE;
L_TRANSACTIONS (1).ORGN_CODE := V_PLANT_CODE;
GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION (
P_API_VERSION => 1,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_TRUE,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_TRAN_REC => L_TRANSACTIONS (1),
X_TRAN_ROW => L_TRANSACTION,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
COMMIT;
DBMS_OUTPUT.PUT_LINE (X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'Return status of create pending transaction'
|| X_RETURN_STATUS
|| ' '
|| X_MSG_COUNT
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
 'Create pending transaction else'
|| SUBSTR (X_MSG_DATA, LENGTH (X_MSG_DATA) - 20, 20)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'ASASDASDASDAS' || X_MATERIAL_DETAIL.MATERIAL_DETAIL_ID
);
COMMIT;
-- TO FETCH THE ITEM CODE AND THE CURRENT LOT NUMBER BASED ON THE
ITEM ID
BEGIN
SELECT ITEM_NO, LOT_SUFFIX
INTO V_ITEM_CODE, V_LOT_NO
FROM IC_ITEM_MST
WHERE ITEM_ID = I.ITEM_ID;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE (FND_FILE.LOG, 'UNABLE TO FIND ITEM');
END;
-- TO DO LINE ALLOCATION OF THE ITEMS SELECTED ABOVE
---------------INSERT INTO LINE ALLOCATION
FOR J IN C_INSERT_LINE_ALLOCATION (V_ITEM_CODE)
LOOP
-- /////////////////////////////////////////////////////////////
-- SET VALUES
-- /////////////////////////////////////////////////////////////
L_TRAN_ROW.DOC_ID := L_BATCH_ID;
L_TRAN_ROW.MATERIAL_DETAIL_ID := V_MATERIAL_DETAIL_ID;
L_TRAN_ROW.WHSE_CODE := J.WHSE_1;
L_TRAN_ROW.TRANS_QTY := J.QTY;
L_TRAN_ROW.TRANS_DATE := V_ACTUAL_DATE;
L_TRAN_ROW.REASON_CODE := J.REASON_CD;
-- /////////////////////////////////////////////////////////////
-- /////////////////////////////////////////////////////////////
-- CALL API METHOD
-- /////////////////////////////////////////////////////////////
GME_API_PUB.INSERT_LINE_ALLOCATION (
P_API_VERSION => GME_API_PUB.API_VERSION,
P_VALIDATION_LEVEL => GME_API_PUB.MAX_ERRORS,
P_INIT_MSG_LIST => TRUE,
P_COMMIT => TRUE,
P_TRAN_ROW => L_TRAN_ROW,
P_LOT_NO => V_LOT_NO,
P_CREATE_LOT => TRUE,
X_MATERIAL_DETAIL => LX_MATERIAL_DETAIL,
X_TRAN_ROW => LX_TRAN_ROW,
X_DEF_TRAN_ROW => LX_DEF_TRAN_ROW,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS
 );
-- /////////////////////////////////////////////////////////////
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_count for line allocation ='
|| TO_CHAR (X_MESSAGE_COUNT)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list for line allocation =' || X_MESSAGE_LIST
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_return_status for line allocation =' || X_RETURN_STATUS
);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id for line allocation = '
|| TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
V_LOT_NO := V_LOT_NO + 1;
END LOOP;
-----------------------------END OF LINE ALLOCATION
-- END OF CREATE
V_NUM := V_NUM + 1;
END IF;
END LOOP;
V_NUM := 0;

--END IF ;
--------------------------------------------RELEASE THE BATCH
-------------------------------------------------------------
L_BATCH_HEADER.PLANT_CODE := V_PLANT_CODE; --'DBP';
L_BATCH_HEADER.BATCH_TYPE := P_BATCH_TYPE;
L_BATCH_HEADER.BATCH_NO :=
V_WHSE_NAME || '/' || P_DATE || '/' || P_SHIFT_CODE || '10';

L_BATCH_HEADER.PLAN_START_DATE := SYSDATE;
L_BATCH_HEADER.PLAN_CMPLT_DATE := SYSDATE;
L_BATCH_HEADER.UPDATE_INVENTORY_IND := 'Y';
L_BATCH_HEADER.RECIPE_VALIDITY_RULE_ID := P_RECEIPE_RULE;
L_BATCH_HEADER.WIP_WHSE_CODE := V_WHSE_CODE;
L_BATCH_HEADER.ACTUAL_START_DATE := V_ACTUAL_DATE;
---------------------------------CALL THE API----------------------------------------------------
GME_API_PUB.RELEASE_BATCH (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_BATCH_HEADER => L_BATCH_HEADER,
X_BATCH_HEADER => X_BATCH_HEADER,
P_IGNORE_SHORTAGES => TRUE,
X_UNALLOCATED_MATERIAL => XX_EXCEPTION_MATERIAL_TBL
);

COMMIT;
FND_FILE.PUT_LINE (FND_FILE.LOG, 'RELEASE BATCH' || X_RETURN_STATUS);
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'RELEASE BATCH =' || TO_CHAR (X_MESSAGE_COUNT));
------------------------------------------------------------------------------------------------
-- IF THE REJECTION ITEM IS NOT A PART OF THE TMS QUERY THEN MAKE IT 0
IF REJECTION_FLAG = 0
THEN
L_BATCH_ID := X_BATCH_HEADER.BATCH_ID;
BEGIN
SELECT MATERIAL_DETAIL_ID
INTO L_MATERIAL_DETAIL_ID
FROM GME_MATERIAL_DETAILS
WHERE BATCH_ID = L_BATCH_ID
AND LINE_TYPE = 1
AND ITEM_ID = V_ITEM_ID;
END;
V_MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.BATCH_ID := L_BATCH_ID;
L_MATERIAL_DETAIL.MATERIAL_DETAIL_ID := L_MATERIAL_DETAIL_ID;
L_MATERIAL_DETAIL.WIP_PLAN_QTY := 0;
L_MATERIAL_DETAIL.ITEM_ID := V_ITEM_ID;
L_INDEX := 1;
L_VALUE_TAB (L_INDEX).P_VALUE := 'WIP_PLAN_QTY';
GME_API_PUB.UPDATE_MATERIAL_LINE (
P_API_VERSION => 1,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
P_INIT_MSG_LIST => FALSE,
P_COMMIT => FALSE,
X_MESSAGE_COUNT => X_MESSAGE_COUNT,
X_MESSAGE_LIST => X_MESSAGE_LIST,
X_RETURN_STATUS => X_RETURN_STATUS,
P_MATERIAL_DETAIL => L_MATERIAL_DETAIL,
P_VALUES_TAB => L_VALUE_TAB,
P_SCALE_PHANTOM => FALSE,
X_MATERIAL_DETAIL => X_MATERIAL_DETAIL
 );
GME_DEBUG.DISPLAY_MESSAGES (X_MESSAGE_COUNT);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'UPDATE LINE =' || TO_CHAR (X_MESSAGE_COUNT));
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_message_list =
'
|| LENGTH (X_MESSAGE_LIST)
|| SUBSTR (X_MESSAGE_LIST, LENGTH (X_MESSAGE_LIST) - 20, 20)
);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'x_message_list =' || X_MESSAGE_LIST);
FND_FILE.PUT_LINE (FND_FILE.LOG,
'x_return_status =' || X_RETURN_STATUS);
FND_FILE.PUT_LINE (
FND_FILE.LOG,
'x_batch_header.batch_id= ' || TO_CHAR (X_MATERIAL_DETAIL.PLAN_QTY)
);
COMMIT;
END IF;
END;
/
TUESDAY, 1 NOVEMBER 2011

xx_tax_load_backup
/* Formatted on 12-08-2011 00:36:38 (QP5 v5.115.810.9015) */
/* CREATE TABLE xx_r12tax_load
(
Operating_unit VARCHAR2 (75),
Po_no VARCHAR2 (25),
po_line_no NUMBER,
tax_name VARCHAR2 (50),
tax_type VARCHAR2 (50),
tax_line_no NUMBER,
precedence_1 NUMBER,
precedence_2 NUMBER,
precedence_3 NUMBER,
precedence_4 NUMBER,
precedence_5 NUMBER,
tax_rate NUMBER,
tax_amount NUMBER,
Loaded_status varchar2(4)
);
DROP TABLE xx_r12tax_load; */
CREATE OR REPLACE PROCEDURE APPS.XX_TAX_LOAD (retcode OUT VARCHAR2,
errbuf OUT VARCHAR2)
IS
CURSOR c1
IS
SELECT * FROM xx_r12tax_load;
 CURSOR c2 (po_header_id IN NUMBER)
IS
SELECT *
FROM po_lines_all
WHERE po_header_id = po_header_id
ORDER BY po_header_id, po_line_id, LINE_NUM;
CURSOR c3 (po_no IN NUMBER, po_line_no NUMBER)
IS
SELECT OPERATING_UNIT,
TO_NUMBER (PO_NO),
PO_LINE_NO,
TAX_NAME,
TAX_TYPE,
TAX_LINE_NO,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
TAX_RATE,
TAX_AMOUNT
FROM xx_r12tax_load
WHERE PO_NO = po_no AND PO_LINE_NO = po_line_no
ORDER BY TO_NUMBER (PO_NO), PO_LINE_NO, TAX_LINE_NO;
v_rec_count NUMBER;
v_operating_unit VARCHAR2 (50);
v_po_count NUMBER;
v_po_header_id NUMBER;
v_po_line_cnt NUMBER;
v_po_line_id NUMBER;
v_po_tax_count NUMBER;
v_po_tax_lne_cnt NUMBER;
v_tax_id VARCHAR2 (50);
v_po_line_locid NUMBER;
v_po_ln_focid NUMBER;
BEGIN
FOR rec1 IN c1
LOOP
DBMS_OUTPUT.put_line( '*************************************'
|| rec1.PO_NO
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| rec1.PO_NO
|| '********************************'
);
BEGIN
SELECT COUNT ( * )
INTO v_rec_count
 FROM hr_operating_units
WHERE name = rec1.OPERATING_UNIT;
END;
IF v_rec_count > 0
THEN
BEGIN
SELECT COUNT ( * ), po_header_id
--------------------------------------------------------------------------------
INTO v_po_count, v_po_header_id
FROM po_headers_all
WHERE segment1 = rec1.Po_no
GROUP BY po_header_id;
IF v_po_count > 0
THEN
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order --> '
|| rec1.PO_NO
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order --> '
|| rec1.PO_NO
|| '********************************'
);
BEGIN
-- FOR rec3 IN c3 (v_po_header_id)
-- LOOP
-- DBMS_OUTPUT.put_line ('Test');
-- END LOOP;
-- SELECT COUNT ( * ), po_line_id
-- INTO v_po_line_cnt, v_po_line_id
-- FROM po_lines_all
-- WHERE po_header_id = v_po_header_id;
-- IF v_po_line_cnt > 0
-- THEN
BEGIN
FOR rec2 IN c2 (v_po_header_id)
LOOP
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Line Details --> '
|| 'PO HEADER ID --> '
|| rec2.po_header_id
|| 'PO LINE ID --> '
|| rec2.po_line_id
|| ' PO LINE NO --> '
|| rec2.LINE_NUM
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
 fnd_file.LOG,
'*************************************'
|| 'Purchase Order Line Details --> '
|| 'PO HEADER ID --> '
|| rec2.po_header_id
|| 'PO LINE ID --> '
|| rec2.po_line_id
|| ' PO LINE NO --> '
|| rec2.LINE_NUM
|| '********************************'
);
/* BEGIN
SELECT line_location_id, line_focus_id
INTO v_po_line_locid, v_po_ln_focid
FROM jai_po_line_locations
WHERE po_header_id = rec2.po_header_id
AND po_line_id = rec2.po_line_id;
DBMS_OUTPUT.put_line(' line_location_id, line_focus_id '
--------------------------------------------------------------------------------
|| v_po_line_locid
|| ' , '
|| v_po_ln_focid);
fnd_file.put_line (
fnd_file.LOG,
' line_location_id, line_focus_id '
|| v_po_line_locid
|| ' , '
|| v_po_ln_focid
);
END; */
BEGIN
FOR rec3 IN c3 (rec1.Po_no, rec2.LINE_NUM)
LOOP
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Tax Line Details --> '
-- || 'PO NO --> '
-- || rec3.PO_NO
-- || 'PO LINE ID --> '
-- || rec3.PO_LINE_NO
-- || 'PO LINE NO --> '
-- || rec3.TAX_LINE_NO
-- || 'PO TAX NAME --> '
-- || rec3.TAX_NAME
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order Tax Line Details --> '
-- || 'PO NO --> '
-- || rec3.PO_NO
 -- || 'PO LINE ID --> '
-- || rec3.PO_LINE_NO
-- || 'PO LINE NO --> '
-- || rec3.TAX_LINE_NO
-- || 'PO TAX NAME --> '
-- || rec3.TAX_NAME
|| '********************************'
);
BEGIN
SELECT COUNT ( * )
--------------------------------------------------------------------------------
INTO v_po_tax_count
FROM JAI_PO_TAXES
WHERE PO_HEADER_ID = v_po_header_id
AND po_line_id = rec2.po_line_id
AND TAX_LINE_NO = rec3.TAX_LINE_NO;
IF v_po_tax_count > 0
THEN
BEGIN
SELECT tax_id
INTO v_tax_id
FROM jai_cmn_taxes_all
WHERE UPPER (tax_name) =
UPPER (rec3.TAX_NAME);
DBMS_OUTPUT.put_line('*************************************'
|| ' Tax ID --> '
|| v_tax_id
|| ' Tax Name --> '
|| UPPER(rec3.TAX_NAME)
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| ' Tax ID --> '
|| v_tax_id
|| ' Tax Name --> '
|| UPPER (rec3.TAX_NAME)
|| '********************************'
);
END;
ELSE
IF rec3.TAX_NAME LIKE '%'
THEN
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Precedence Tax Insert Block --> '
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
 || 'Purchase Order Precedence Tax Insert Block --> '
|| '********************************'
);
INSERT INTO jai_po_taxes (
TAX_ID,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
PO_HEADER_ID,
po_line_id,
TAX_LINE_NO,
TAX_TYPE,
TAX_AMOUNT,
LINE_FOCUS_ID,
LINE_LOCATION_ID
)
VALUES (v_tax_id,
rec3.PRECEDENCE_1,
rec3.PRECEDENCE_2,
rec3.PRECEDENCE_3,
rec3.PRECEDENCE_4,
rec3.PRECEDENCE_5,
v_po_header_id,
rec2.po_line_id,
rec3.TAX_LINE_NO,
rec3.TAX_TYPE,
rec3.TAX_AMOUNT,
13620,
167001);
COMMIT;
ELSE
BEGIN
DBMS_OUTPUT.put_line('*************************************'
|| 'Purchase Order Lumpsum Tax Insert Block --> '
|| '********************************');
--------------------------------------------------------------------------------
fnd_file.put_line (
fnd_file.LOG,
'*************************************'
|| 'Purchase Order Lumpsum Tax Insert Block --> '
|| '********************************'
);
INSERT INTO jai_po_taxes (
TAX_ID,
PO_HEADER_ID,
po_line_id,
TAX_LINE_NO,
TAX_AMOUNT,
TAX_TYPE
 )
VALUES (v_tax_id,
v_po_header_id,
rec2.po_line_id,
rec3.TAX_LINE_NO,
rec3.TAX_AMOUNT,
rec3.TAX_TYPE);
COMMIT;
END;
END IF;
END IF;
END;
END LOOP;
END;
END LOOP;
END;
-- ELSE
-- DBMS_OUTPUT.put_line ('Test');
-- END IF;
END;
ELSE
DBMS_OUTPUT.put_line('PO Not Exists In Oracle Base tb :'
|| rec1.PO_NO);
fnd_file.put_line --------------------------------------------------------------------------------
(
fnd_file.LOG,
'PO Not Exists In Oracle Base tb :'
|| rec1.PO_NO
);
END IF;
DBMS_OUTPUT.put_line ('Before end of the loop');
END;
ELSE
DBMS_OUTPUT.put_line ('End of the Loop ');
END IF;
END LOOP;
END;
Posted by Elangovan Ragavan at 15:16 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

MONDAY, 31 OCTOBER 2011

JobCode_Cost_
Select MATCH_OPTION from po_line_locations_all where po_line_id=137873
Select * from pa_projects_all
Select * from all_objects where object_name like '%PROJECTS%'
Select * from FND_FLEX_VALUES_VL where flex_value_set_id ='1005233'
Select * from fnd_flex_values where flex_value_id=33584
Select * from fnd_flex_values_tl where flex_value_id=33584
Select * from gl_code_combinations_kfv where segment6='103P' and segment1='42'
/* Formatted on 31-08-2011 15:39:43 (QP5 v5.115.810.9015) */
/* Formatted on 31-08-2011 15:40:41 (QP5 v5.115.810.9015) */
SELECT DISTINCT hou.name Operating_unit,
glcc.segment6 job_code,
aia.invoice_num,
aia.INVOICE_DATE,
aia.INVOICE_TYPE_LOOKUP_CODE,
aia.GL_DATE,
aia.invoice_amount,
glcc.CONCATENATED_SEGMENTS Acct_Code
FROM gl_code_combinations_kfv glcc,
ap_invoices_all aia,
ap_invoice_distributions_All aid,
hr_operating_units hou
WHERE aia.set_of_books_id = 5
AND aid.DIST_CODE_COMBINATION_ID = glcc.CODE_COMBINATION_ID
AND aia.invoice_id = aid.invoice_id
AND aia.org_id = aid.org_id
AND hou.organization_id = aia.org_id
AND glcc.segment6 IN
('103P',
'111P ',
'114P ',
'112P ',
'101P',
'102P',
'104P',
'105P',
'106P',
'106P',
'110P',
'104P',
'105P',
'109P',
'55CC',
'54CC',
'63CC',
'57CC',
'58CC',
'61CC',
'62CC',
'59CC',
'103P',
'50CC')
Posted by Elangovan Ragavan at 17:51 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

DOC_SEQUENCE_QRY
/* Formatted on 17-09-2011 13:31:52 (QP5 v5.115.810.9015) */
SELECT seq.NAME,
seq.db_sequence_name,
 seq.initial_value,
ds.LAST_NUMBER,
seq.table_name,
seq.start_date,
seq.end_date,
app.APPLICATION_SHORT_NAME,
DECODE (asgn.METHOD_CODE,
'A',
'Automatic',
'M',
'Manual')
Method,
cat.CODE Category
FROM FND_DOCUMENT_SEQUENCES seq,
fnd_Application app,
FND_DOC_SEQUENCE_CATEGORIES cat,
FND_DOC_SEQUENCE_ASSIGNMENTS asgn,
dba_sequences ds
WHERE app.application_id = seq.application_id
AND asgn.DOC_SEQUENCE_ID = seq.DOC_SEQUENCE_ID
AND cat.code = asgn.CATEGORY_CODE
AND ds.SEQUENCE_NAME = seq.DB_SEQUENCE_NAME

--Select * from dba_sequences

Posted by Elangovan Ragavan at 16:41 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

Receipt_Count_R12
Select * from xx_receipt_no
Select * from rcv_shipment_headers where receipt_num='425003788'
SELECT shipment_header_id,receipt_num
FROM rcv_shipment_headers
WHERE receipt_num IN
(SELECT receipt_no FROM xx_receipt_no)

Select * from jai_rcv_line_taxes

SELECT receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id IN
(SELECT shipment_header_id
FROM jai_rcv_line_taxes)
order by (receipt_num)
/* Formatted on 26-08-2011 11:44:14 (QP5 v5.115.810.9015) */
SELECT *
FROM jai_rcv_line_taxes
WHERE shipment_header_id IN
(SELECT shipment_header_id
FROM rcv_shipment_headers
WHERE receipt_num not IN
(SELECT receipt_no FROM xx_receipt_no))
SELECT receipt_no
FROM xx_receipt_no
WHERE RECEIPT_NO IN
(SELECT receipt_num
FROM rcv_shipment_headers
WHERE shipment_header_id IN
(SELECT shipment_header_id
FROM jai_rcv_line_taxes))
Posted by Elangovan Ragavan at 16:31 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12_USERS
/* Formatted on 17-08-2011 11:04:56 (QP5 v5.115.810.9015) */
SELECT emp.EMPLOYEE_ID, FULL_NAME, USER_NAME
FROM hr_employees emp, fnd_user usr
WHERE emp.EMPLOYEE_ID = usr.EMPLOYEE_ID

Posted by Elangovan Ragavan at 16:24 1 comment:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12_User_Responsibility
/* Formatted on 27-08-2011 12:19:51 (QP5 v5.115.810.9015) */
SELECT DISTINCT u.user_id,u.user_name, rtl.RESPONSIBILITY_NAME,
emp.EMAIL_ADDRESS
FROM fnd_responsibility r,
fnd_responsibility_TL rtl,
fnd_user_resp_groups urg,
fnd_user u,
hr_employees emp
WHERE urg.responsibility_id = r.responsibility_id
AND rtl.responsibility_id = r.responsibility_id
AND r.APPLICATION_ID = urg.RESPONSIBILITY_APPLICATION_ID
AND u.user_id = urg.user_id
AND emp.EMPLOYEE_ID = u.EMPLOYEE_ID
and u.employee_id not in (1130)
AND u.employee_id IN
(81,
82,
83,
86,
87,
88,
89,
90,
91,
92,
121,
141,
161,
201,
221,
222,
225,
241,
261,
262,
263,
264,
265,
266,
267,
268,
269,
270,
271,
281,
302,
341,
361,
381,
382,
383,
384,
386,
388,
401,
421,
441,
461,
462,
481,
501,
521,
522,
523,
524,
525,
526,
527,
528,
529,
530,
531,
532,
533,
541,
542,
 561,
562,
563,
564,
565,
566,
567,
570,
571,
572)
ORDER BY u.user_name
Posted by Elangovan Ragavan at 16:23 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12_Suppliers
/* Formatted on 05-09-2011 12:14:30 (QP5 v5.115.810.9015) */
SELECT hou.organization_id,
hou.name Operating_Unit,
--POV.ATTRIBUTE5 R11i_Vendor_id,
TO_NUMBER (pov.segment1) Vendor_Code,
pov.vendor_name Vendor,
pov.VENDOR_TYPE_LOOKUP_CODE Vendor_Type,
--pvs.VENDOR_SITE_ID R11i_vendor_site_id,
pvs.VENDOR_SITE_CODE,
(SELECT CONCATENATED_SEGMENTS
FROM gl_code_combinations_kfv
WHERE CODE_COMBINATION_ID = pvs.PREPAY_CODE_COMBINATION_ID)
Prepay,
(SELECT CONCATENATED_SEGMENTS
FROM gl_code_combinations_kfv
WHERE CODE_COMBINATION_ID = pvs.ACCTS_PAY_CODE_COMBINATION_ID)
Liability
FROM po_vendors pov, po_vendor_sites_all pvs, hr_operating_units hou
WHERE pov.vendor_id = pvs.vendor_id AND pvs.org_id = hou.organization_id
ORDER BY hou.organization_id, TO_NUMBER (pov.segment1)
--Select * from po_vendors where segment1='1802'

NEW
----------
/* Formatted on 9/26/2011 5:55:22 PM (QP5 v5.115.810.9015) */
SELECT hou.organization_id,
hou.name Operating_Unit,
--POV.ATTRIBUTE5 R11i_Vendor_id,
TO_NUMBER (pov.segment1) Vendor_Code,
pov.vendor_name Vendor,
pvs.ADDRESS_LINE1,
pvs.ADDRESS_LINE2,
pvs.ADDRESS_LINE3,
pvs.ADDRESS_LINE4,
pov.VENDOR_TYPE_LOOKUP_CODE Vendor_Type,
 --pvs.VENDOR_SITE_ID R11i_vendor_site_id,
pvs.VENDOR_SITE_CODE,
(SELECT CONCATENATED_SEGMENTS
FROM gl_code_combinations_kfv
WHERE CODE_COMBINATI2ON_ID = pvs.PREPAY_CODE_COMBINATION_ID)
Prepay,
(SELECT CONCATENATED_SEGMENTS
FROM gl_code_combinations_kfv
WHERE CODE_COMBINATION_ID = pvs.ACCTS_PAY_CODE_COMBINATION_ID)
Liability
FROM po_vendors pov, po_vendor_sites_all pvs, hr_operating_units hou
WHERE pov.vendor_id = pvs.vendor_id AND pvs.org_id = hou.organization_id
ORDER BY hou.organization_id, TO_NUMBER (pov.segment1)

NEW
------------
SELECT hou.organization_id,
hou.name Operating_Unit,
--POV.ATTRIBUTE5 R11i_Vendor_id,
TO_NUMBER (pov.segment1) Vendor_Code,
pov.vendor_name Vendor,
pvs.ADDRESS_LINE1,
pvs.ADDRESS_LINE2,
pvs.ADDRESS_LINE3,
pvs.ADDRESS_LINE4,
PVS.ADDRESS_LINES_ALT,
PVS.CITY,
PVS.STATE,
pov.VENDOR_TYPE_LOOKUP_CODE Vendor_Type,
--pvs.VENDOR_SITE_ID R11i_vendor_site_id,
pvs.VENDOR_SITE_CODE,
pvs.INVOICE_AMOUNT_LIMIT,
pvs.PAY_ON_CODE,
pvs.PAY_ON_RECEIPT_SUMMARY_CODE,
pvs.PREPAY_CODE_COMBINATION_ID,
(SELECT NAME
FROM ap_terms
WHERE TERM_ID = pvs.TERMS_ID)
TERM_NAME,
pvs.CREATE_DEBIT_MEMO_FLAG,
pvs.PURCHASING_SITE_FLAG,
pvs.PAY_SITE_FLAG,
(SELECT CONCATENATED_SEGMENTS
FROM gl_code_combinations_kfv
WHERE CODE_COMBINATION_ID = pvs.PREPAY_CODE_COMBINATION_ID)
Prepay,
(SELECT CONCATENATED_SEGMENTS
FROM gl_code_combinations_kfv
WHERE CODE_COMBINATION_ID = pvs.ACCTS_PAY_CODE_COMBINATION_ID)
Liability
FROM po_vendors pov, po_vendor_sites_all pvs, hr_operating_units hou
 WHERE pov.vendor_id = pvs.vendor_id AND pvs.org_id = hou.organization_id
ORDER BY hou.organization_id, TO_NUMBER (pov.segment1)
MONDAY, 31 OCTOBER 2011

R12_Responsibility_With_Funtion
/* Formatted on 17-08-2011 16:01:37 (QP5 v5.115.810.9015) */
SELECT DISTINCT u.user_name,
rtl.RESPONSIBILITY_NAME,
ff.function_name,
ffl.user_function_name
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_TL rtl,
fnd_user_resp_groups urg,
fnd_user u,
hr_employees emp
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND urg.responsibility_id = r.responsibility_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.GRANT_FLAG = 'Y'
AND r.APPLICATION_ID = urg.RESPONSIBILITY_APPLICATION_ID
AND u.user_id = urg.user_id
--and ff.function_id=19438
--AND UPPER (ffl.user_function_name) LIKE UPPER ('Agent%Dashboard')
AND emp.EMPLOYEE_ID = u.EMPLOYEE_ID
AND ff.function_id = ffl.function_id
ORDER BY u.user_name
Posted by Elangovan Ragavan at 16:18 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12_profile_options_query
SELECT B.APPLICATION_ID,
a.APPLICATION_NAME,
d.APPLICATION_SHORT_NAME,
b.PROFILE_OPTION_ID,
b.PROFILE_OPTION_NAME,
T.USER_PROFILE_OPTION_NAME,
d.BASEPATH,
T.DESCRIPTION
FROM FND_PROFILE_OPTIONS_TL T, FND_PROFILE_OPTIONS B,fnd_application_tl
a,fnd_application D
WHERE B.PROFILE_OPTION_NAME = T.PROFILE_OPTION_NAME
AND a.APPLICATION_ID = b.APPLICATION_ID
AND a.APPLICATION_ID = d.APPLICATION_ID
ORDER BY application_id
Posted by Elangovan Ragavan at 16:17 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12_PO_Query_v1.0
/* Formatted on 27-08-2011 12:44:36 (QP5 v5.115.810.9015) */
SELECT DISTINCT PHA.SEGMENT1 PO_NO,
TRUNC(PHA.CREATION_DATE)PO_DATE,
HOU.NAME OPERATING_UNI,
OOD.ORGANIZATION_CODE,
OOD.ORGANIZATION_NAME
FROM po_headers_all PHA,
po_lines_all PLA,
po_line_locations_all PLLA,
ORG_ORGANIZATION_DEFINITIONS OOD,
HR_OPERATING_UNITS HOU
WHERE PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PHA.PO_HEADER_ID = PLLA.PO_HEADER_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PHA.ORG_ID = PLA.ORG_ID
AND OOD.ORGANIZATION_ID = PLLA.SHIP_TO_ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = OOD.OPERATING_UNIT
AND TRUNC (PHA.creation_date) BETWEEN '01-Apr-2011'
AND '13-Aug-2011'
ORDER BY TO_NUMBER (PHA.segment1), TRUNC (PHA.creation_date)
Posted by Elangovan Ragavan at 16:16 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12_PO_Query
/* Formatted on 27-08-2011 12:16:00 (QP5 v5.115.810.9015) */
SELECT DISTINCT poh.org_id,
hou.name,
poh.segment1,
TRUNC (poh.creation_date),
ood.organization_code
FROM po_headers_all poh,
hr_operating_units hou,
po_lines_all pol,
po_line_locations_all pll,
org_organization_definitions ood
WHERE poh.org_id = hou.organization_id
AND poh.po_header_id = pol.po_header_id
AND pll.po_header_id = poh.po_header_id
AND pol.po_line_id = pll.po_line_id
AND poh.org_id = pol.org_id
AND ood.organization_id = pll.SHIP_TO_ORGANIZATION_ID
AND TRUNC (poh.creation_date) BETWEEN '01-Apr-2011'
AND '31-jul-2011'
ORDER BY poh.org_id, TO_NUMBER (poh.segment1), TRUNC (poh.creation_date)
Posted by Elangovan Ragavan at 16:15 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12_Chart_of_Accounts
/* Formatted on 30-08-2011 13:44:53 (QP5 v5.115.810.9015) */
SELECT hou.name,
seg.application_column_name,
seg.segment_name,
seg.SEGMENT_NUM,
seg.flex_value_set_id
FROM apps.hr_operating_units hou
,apps.gl_ledgers gled
,FND_ID_FLEX_SEGMENTS seg
--,fnd_flex_values_vl ffv
WHERE gled.ledger_id = hou.set_of_books_id
AND seg.id_flex_code = 'GL#'
AND seg.id_flex_num = gled.CHART_OF_ACCOUNTS_ID
--and hou.organization_id=4

select * from gl_code_combinations_kfv

/* Formatted on 31-08-2011 17:03:27 (QP5 v5.115.810.9015) */
SELECT *
FROM fnd_flex_value_sets
WHERE FLEX_VALUE_SET_ID = 1014869

SELECT ffv.FLEX_VALUE,
fft.FLEX_VALUE_MEANING,
fft.DESCRIPTION
--,kfv.CONCATENATED_SEGMENTS
FROM fnd_flex_values ffv,
fnd_flex_values_tl fft
WHERE ffv.FLEX_VALUE_ID = fft.FLEX_VALUE_ID
--AND kfv.SEGMENT2 = ffv.FLEX_VALUE
AND FLEX_VALUE_SET_ID = 1014869

Select * from fnd_flex_values fft , fnd_flex_values_vl ffv

where fft.FLEX_VALUE_ID=ffv.FLEX_VALUE_ID
--and rownum <=10
Select * from fnd_flex_values_vl where FLEX_VALUE_SET_ID= '1014874'
Select * from fnd_flex_values where flex_value_id=58331
Select * from fnd_flex_value_sets where FLEX_VALUE_SET_ID= '1014874'
Posted by Elangovan Ragavan at 16:12 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12_Buyers
/* Formatted on 09-08-2011 13:23:10 (QP5 v5.115.810.9015) */
SELECT DISTINCT --ASSIGNMENT_NUMBER,
(SELECT FULL_NAME
FROM PER_PEOPLE_V7
 WHERE PERSON_ID = pp.PERSON_ID)
Buyer_Name, hou.name Operating_unit
FROM PER_PERIODS_OF_SERVICE_V2 pp, HR_OPERATING_UNITS HOU
WHERE --person_id = 462 AND (PERSON_ID = 462) AND
HOU.BUSINESS_GROUP_ID = pp.BUSINESS_GROUP_ID
AND set_of_books_id NOT IN (2046, 2049)

--ORDER BY date_start DESC, assignment_number DESC

--Select * from hr_operating_units
Posted by Elangovan Ragavan at 15:59 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12_AR_Invoice_Data
/* Formatted on 17-08-2011 22:20:14 (QP5 v5.115.810.9015) */
SELECT hou.name Operating_unit,
typ.NAME Transaction_Type,
rcta.CUSTOMER_TRX_ID,
TRX_NUMBER,
TRX_DATE,
line.EXTENDED_AMOUNT
FROM ra_customer_trx_all rcta,
ra_customer_trx_lines_all line,
hr_operating_units hou,
RA_CUST_TRX_TYPES_ALL typ,
ra_cust_trx_line_gl_dist_all dist
WHERE RCTA.CUSTOMER_TRX_ID = line.CUSTOMER_TRX_ID
AND hou.organization_id = rcta.org_id
AND typ.CUST_TRX_TYPE_ID = rcta.CUST_TRX_TYPE_ID
and rcta.CUSTOMER_TRX_ID=dist.CUSTOMER_TRX_ID
and dist.CUSTOMER_TRX_LINE_ID=line.CUSTOMER_TRX_LINE_ID
and trunc(GL_DATE) <= '31-MAR-2011'
--AND RCTA.CUSTOMER_TRX_ID = 1000
Posted by Elangovan Ragavan at 15:57 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12 Imo_Subinv_Ohq_Qry
Select * from mtl_system_items_b where ORGANIZATION_ID=117
Select * from org_organization_definitions
SELECT DISTINCT msi.SEGMENT1 New_Item_Code,
msi.description,
msi.attribute6 Old_Item_Code,
msi.PRIMARY_UNIT_OF_MEASURE UOM_Code,
kfv.CONCATENATED_SEGMENTS Item_Category
FROM mtl_system_items_b msi,
mtl_categories_kfv kfv,
mtl_item_categories mic
WHERE msi.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID
AND mic.CATEGORY_ID = kfv.CATEGORY_ID
AND mic.ORGANIZATION_ID = msi.ORGANIZATION_ID
 AND msi.ORGANIZATION_ID = 117
--and msi.attribute6 is not null

SELECT distinct ORGANIZATION_NAME,

SECONDARY_INVENTORY_NAME,
mts.DESCRIPTION,
mil.CONCATENATED_SEGMENTS Locator_Code
FROM mtl_secondary_inventories mts,
org_organization_definitions ood,
MTL_ITEM_LOCATIONS_kfv mil,
MTL_ONHAND_QUANTITIES_DETAIL moq
WHERE mts.ORGANIZATION_ID = ood.ORGANIZATION_ID
AND moq.ORGANIZATION_ID = ood.ORGANIZATION_ID
AND moq.LOCATOR_ID = mil.INVENTORY_LOCATION_ID(+)

Posted by Elangovan Ragavan at 15:54 1 comment:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

po_receipt_with_accounting
SELECT /*+INDEX (RSH RCV_SHIPMENT_HEADERS_U1) INDEX (GCCK
GL_CODE_COMBINATIONS_U1)*/
rsh.receipt_num, rt.transaction_type, NULL account_type,
gcck.concatenated_segments, rrsl.accounted_cr, rrsl.accounted_dr,
rrsl.currency_code, ood.organization_code organization_code,
rrsl.accounting_date gl_date
FROM rcv_receiving_sub_ledger rrsl,
rcv_transactions rt,
rcv_shipment_headers rsh,
gl_code_combinations_kfv gcck,
org_organization_definitions ood
WHERE rrsl.rcv_transaction_id = rt.transaction_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND rrsl.code_combination_id = gcck.code_combination_id
AND rsh.organization_id = ood.organization_id
AND rrsl.accounting_date between :p_from_date and :to_from_date
AND ood.organization_code = :organization_code
UNION
SELECT jirjb.receipt_num, jirjb.acct_nature, jirjb.acct_type,
gcck.concatenated_segments, jirjb.entered_cr, jirjb.entered_dr,
jirjb.currency_code, ood.organization_code organization_code,
jirjb.transaction_date gl_date
FROM ja_in_rcv_journals_b jirjb,
gl_code_combinations_kfv gcck,
org_organization_definitions ood
WHERE jirjb.code_combination_id = gcck.code_combination_id
AND jirjb.organization_code = ood.organization_code
AND jirjb.transaction_date between :p_from_date and :to_from_date
AND ood.organization_code = :organization_code
Posted by Elangovan Ragavan at 15:48 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

R12_PO_RECEIPT_TXN_Qry
/* Formatted on 24-09-2011 11:33:58 (QP5 v5.115.810.9015) */
SELECT DISTINCT
hou.name Operating_unit,
hou.organization_id,
ood.organization_name,
pov.segment1 vendor_code,
pov.vendor_name,
rsh.RECEIPT_NUM RECEIPT_NO,
rsl.LINE_NUM RECEIPT_LINE_NO,
rsh.SHIPMENT_NUM L_SHIPMENT_NUM,
rt.TRANSACTION_TYPE L_TRANSACTION_TYPE,
rt.TRANSACTION_DATE L_TRANSACTION_DATE,
poh.SEGMENT1 L_PO_NUMBER,
pol.LINE_NUM L_PO_LINE_NUM,
rt.QUANTITY L_QUANTITY,
rt.UNIT_OF_MEASURE L_UOM,
(SELECT segment1
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = pol.item_id
AND organization_id = ood.organization_id)
L_ITEM_NUM,
rt.SUBINVENTORY L_SUBINVENTORY,
rt.DESTINATION_TYPE_CODE L_DESTINATION_TYPE,
pol.ITEM_DESCRIPTION,
loc.LOCATION_CODE L_SHIP_TO_LOCATION,
rsh.SHIPMENT_NUM L_SHIPMENT_NUM
FROM rcv_transactions rt,
org_organization_definitions ood,
RCV_SHIPMENT_HEADERS RSH,
po_headers_all poh,
po_lines_all pol,
HR_LOCATIONS_ALL_TL loc,
po_line_locations_all poll,
hr_operating_units hou,
po_vendors pov,
rcv_shipment_lines rsl
WHERE rt.ORGANIZATION_ID = ood.ORGANIZATION_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND rsh.SHIPMENT_HEADER_ID = RSl.SHIPMENT_HEADER_ID
AND rsl.SHIPMENT_LINE_ID = rt.SHIPMENT_LINE_ID
AND rt.PO_HEADER_ID = poh.PO_HEADER_ID
AND poh.po_header_id = pol.po_header_id
AND rt.po_line_id = pol.po_line_id
AND poll.PO_HEADER_ID = poh.PO_HEADER_ID
AND poll.po_line_id = pol.po_line_id
AND poll.SHIP_TO_LOCATION_ID = loc.LOCATION_ID
AND hou.organization_id = ood.operating_unit
 AND pov.vendor_id = poh.vendor_id
and rsl.TO_ORGANIZATION_ID=ood.ORGANIZATION_ID
AND ood.set_of_books_id = 5
AND RT.TRANSACTION_TYPE = 'DELIVER'
/*AND TRUNC (rt.transaction_date) BETWEEN '01-Apr-2011'
AND '13-Aug-2011' */
/*AND RT.TRANSACTION_ID IN ( SELECT MAX (TRANSACTION_ID)
FROM RCV_TRANSACTIONS
GROUP BY SHIPMENT_LINE_ID) */
AND LENGTH (rsh.RECEIPT_NUM) = 9
ORDER BY TO_NUMBER (rsh.RECEIPT_NUM),rsl.LINE_NUM, pol.LINE_NUM
Posted by Elangovan Ragavan at 15:47 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

GL_SEGMENT_WITH_BY ORG
/* Formatted on 14-09-2011 11:25:38 (QP5 v5.115.810.9015) */
SELECT DECODE (gcc.segment1,
'10',
'APIL - HO - 10',
'20',
'APIL - BPU UNIT - 20',
'21',
'APIL - BPU UNIT - 21',
'30',
'APIL - SHREE GOPAL UNIT - 30',
'40',
'APIL - SEWA UNIT - 40',
'50',
'APIL - BWN - 50',
'51',
'APIL - BWN - 51')
OPERATING_UNIT,
ffv.FLEX_VALUE NATURAL_ACCT,641449
ffv.description,
ffv.HIERARCHY_LEVEL,
ffv.FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUES_VL ffv, gl_code_combinations gcc
WHERE ( ('' IS NULL)
OR (ffv.structured_hierarchy_level IN
(SELECT hierarchy_id
FROM fnd_flex_hierarchies_vl h
WHERE h.flex_value_set_id = 1014870
AND h.hierarchy_name LIKE '')))
AND gcc.segment3 = FLEX_VALUE
AND ffv.FLEX_VALUE_SET_ID = 1014870
-- AND ffv.END_DATE_ACTIVE IS NULL
and ffv.END_DATE_ACTIVE IS not NULL
ORDER BY gcc.segment1 -- ffv.flex_value

NEW
--------------
SELECT DECODE (gcc.segment1,
'10',
'APIL - HO - 10',
'20',
'APIL - BPU UNIT - 20',
'21',
'APIL - BPU UNIT - 21',
'30',
'APIL - SHREE GOPAL UNIT - 30',
'40',
'APIL - SEWA UNIT - 40',
'50',
'APIL - BWN - 50',
'51',
'APIL - BWN - 51')
OPERATING_UNIT,
ffv.FLEX_VALUE NATURAL_ACCT,
ffv.description,
ffv.HIERARCHY_LEVEL,
ffv.FLEX_VALUE_SET_ID,
ffv.COMPILED_VALUE_ATTRIBUTES
FROM FND_FLEX_VALUES_VL ffv, gl_code_combinations gcc
WHERE ( ('' IS NULL)
OR (ffv.structured_hierarchy_level IN
(SELECT hierarchy_id
FROM fnd_flex_hierarchies_vl h
WHERE h.flex_value_set_id = 1014870
AND h.hierarchy_name LIKE '')))
AND gcc.segment3 = FLEX_VALUE
AND ffv.FLEX_VALUE_SET_ID = 1014870
-- AND ffv.END_DATE_ACTIVE IS NULL
and ffv.END_DATE_ACTIVE IS not NULL
and ffv.COMPILED_VALUE_ATTRIBUTES is not null
ORDER BY gcc.segment1 -- ffv.flex_value

select * from all_tab_columns where column_name = 'HIERARCHY_ID' -- like --

COMPILED_VALUE_ATTRIBUTES

select * from FND_FLEX_HIERARCHIES

select * from FND_FLEX_HIERARCHIES_TL
select * from FND_FLEX_VALUES where COMPILED_VALUE_ATTRIBUTES is not null

SELECT DECODE (gcc.segment1,

'10',
'APIL - HO - 10',
'20',
'APIL - BPU UNIT - 20',
'21',
'APIL - BPU UNIT - 21',
'30',
 'APIL - SHREE GOPAL UNIT - 30',
'40',
'APIL - SEWA UNIT - 40',
'50',
'APIL - BWN - 50',
'51',
'APIL - BWN - 51')
OPERATING_UNIT, ffv.FLEX_VALUE_MEANING NATURAL_ACCT, ffv.DESCRIPTION
ACCT_DESC
FROM gl_code_combinations gcc, fnd_flex_values_vl ffv
WHERE gcc.segment3 = ffv.FLEX_VALUE
AND ffv.FLEX_VALUE_SET_ID = 1014870
AND ffv.END_DATE_ACTIVE IS NULL --or AND ffv.END_DATE_ACTIVE IS NULL
AND gcc.segment1 IN ('10', '20', '21', '30', '40', '50', '51')
ORDER BY TO_NUMBER (gcc.segment1)
Posted by Elangovan Ragavan at 15:46 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

Supplier_Sites
SELECT DISTINCT --replace(hr.name,'BILT','APIL') OU_NAME,
b.Org_id ORG_ID,
UPPER(hr.name) OU_NAME,
UPPER (A.VENDOR_NAME) S_VENDOR_NAME,
A.VENDOR_ID S_OLD_REFERENCE,
UPPER (a.VENDOR_TYPE_LOOKUP_CODE) VENDOR_TYPE_LOOKUP_CODE,
A.SEGMENT1 VENDOR_NUMBER,
UPPER (B.VENDOR_SITE_CODE) S_SITE_CODE,
UPPER (B.COUNTRY) S_COUNTRY,
UPPER (B.ADDRESS_LINE1) S_ADDRESS_LINE1,
UPPER (B.ADDRESS_LINE2) S_ADDRESS_LINE2,
UPPER (B.ADDRESS_LINE3) S_ADDRESS_LINE3,
UPPER (B.ADDRESS_LINE4) S_ADDRESS_LINE4,
UPPER (B.CITY) S_CITY,
UPPER (B.COUNTY) S_COUNTY,
UPPER (B.STATE) S_STATE,
B.ZIP S_POSTAL_CODE,
UPPER (B.PURCHASING_SITE_FLAG) S_PURCHASING_SITE,
UPPER (B.PAY_SITE_FLAG) S_PAYMENT_SITE,
B.RFQ_ONLY_SITE_FLAG S_RFQ_ONLY_SITE,
B.AREA_CODE S_AREA_CODE,
B.PHONE S_PHONE,
B.FAX_AREA_CODE S_FAX_AREA_CODE,
B.FAX S_FAX,
UPPER (B.EMAIL_ADDRESS) S_EMAIL_ADDRESS,
UPPER (B.TERMS_DATE_BASIS) S_TERMS_DATE_BASIS,
D1.CONCATENATED_SEGMENTS S_ACCTS_PAY_CODE_COMBINATION,
D2.CONCATENATED_SEGMENTS S_PREPAY_CODE_COMBINATION,
B.PAY_GROUP_LOOKUP_CODE S_PAY_GROUP_LOOKUP_CODE,
UPPER (C.NAME) S_PAYMENT_TERMS,
UPPER (B.PAY_DATE_BASIS_LOOKUP_CODE)
S_PAY_DATE_BASIS_LOOKUP_CODE,
UPPER (B.INVOICE_CURRENCY_CODE) S_INVOICE_CURRENCY_CODE,
UPPER (B.PAYMENT_CURRENCY_CODE) S_PAYMENT_CURRENCY_CODE,
UPPER (B.ATTRIBUTE_CATEGORY) S_ATTRIBUTE_CATEGORY,
UPPER (B.ATTRIBUTE1) S_ATTRIBUTE1,
UPPER (B.ATTRIBUTE2) S_ATTRIBUTE2,
UPPER (B.ATTRIBUTE3) S_ATTRIBUTE3,
UPPER (B.ATTRIBUTE4) S_ATTRIBUTE4,
B.VENDOR_SITE_ID Old_ref_site_id,
UPPER (B.ATTRIBUTE6) S_ATTRIBUTE6,
UPPER (B.ATTRIBUTE7) S_ATTRIBUTE7,
UPPER (B.ATTRIBUTE8) S_ATTRIBUTE8,
UPPER (B.ATTRIBUTE9) S_ATTRIBUTE9,
UPPER (B.ATTRIBUTE10) S_ATTRIBUTE10,
UPPER (B.ATTRIBUTE11) S_ATTRIBUTE11,
UPPER (B.ATTRIBUTE12) S_ATTRIBUTE12,
UPPER (B.ATTRIBUTE13) S_ATTRIBUTE13,
UPPER (B.ATTRIBUTE14) S_ATTRIBUTE14,
UPPER (B.PAY_ON_RECEIPT_SUMMARY_CODE)
S_PAY_ON_RECEIPT_SUMMARY_CODE,
UPPER (B.PAY_ON_CODE) S_PAY_ON_CODE,
DECODE (B.MATCH_OPTION, 'R', 'Receipt', 'P', 'Purchase Order', NULL)
S_MATCH_OPTION,
B.CREATE_DEBIT_MEMO_FLAG S_CREATE_DEBIT_MEMO_FLAG,
UPPER (B.PAYMENT_METHOD_LOOKUP_CODE)
S_PAYMENT_METHOD_LOOKUP_CODE
FROM apps.PO_VENDORS A,
apps.PO_VENDOR_SITES_ALL B,
apps.AP_TERMS C,
apps.GL_CODE_COMBINATIONS_KFV D1,
apps.GL_CODE_COMBINATIONS_KFV D2,
apps.hr_operating_units hr
WHERE A.VENDOR_ID = B.VENDOR_ID
AND A.TERMS_ID = C.TERM_ID(+)
AND B.ACCTS_PAY_CODE_COMBINATION_ID = D1.CODE_COMBINATION_ID(+)
AND B.PREPAY_CODE_COMBINATION_ID = D2.CODE_COMBINATION_ID(+)
AND a.END_DATE_ACTIVE IS NULL
AND b.INACTIVE_DATE IS NULL
AND b.org_id = hr.organization_id
--AND hr.SET_OF_BOOKS_ID=5
and a.vendor_name='AIRBREAK SWITCHGEARS'

AND a.vendor_id IN (15685, 18484, 51547, 64242, 66436, 69531)

--and hr.set_of_books_id=5
--and a.VENDOR_TYPE_LOOKUP_CODE NOT IN ('Ex-Employee', 'EX-
EMPLOYEE','Employee','EMPLOYEE','CONSULTANT')
--'Employee','EMPLOYEE','EMPLOYEERM',
--and a.VENDOR_TYPE_LOOKUP_CODE IN ('Ex-Employee', 'EX-EMPLOYEE')
--AND b.org_id IN (1280, 1202, 1203, 1204, 1205) -- = :
 --and upper(a.vendor_name) like '%PRASA%'

AND a.vendor_id IN
(SELECT vendor_id
FROM po_vendors
WHERE vendor_id IN
(SELECT VENDOR_NO FROM xx_vendor_mst_200))
order by to_number(a.segment1)

AND a.vendor_id IN
(SELECT vendor_id
FROM po_vendors
WHERE VENDOR_NAME IN
(SELECT a1.VENDOR_NAME
FROM xx_vendor_dtls_90 a1,
xx_vendor_dtls_90 a2
WHERE a1.vendor_name LIKE
'%'
|| ''
|| a2.vendor_name
|| ''
|| '%'))
ORDER BY TO_NUMBER (a.segment1)
--Select distinct VENDOR_TYPE_LOOKUP_COD
E from po_vendors
Posted by Elangovan Ragavan at 15:43 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

Supplier_Master
/* Formatted on 16-08-2011 20:20:28 (QP5 v5.115.810.9015) */
SELECT DISTINCT
hr.organization_id, --pv.org_id,
UPPER (HR.NAME) OPERATING_UNIT,
UPPER (A.VENDOR_NAME) VENDOR_NAME,
A.VENDOR_NUMBER,
UPPER (A.VENDOR_NAME_ALT) ALTERNATE_NAME,
NULL Alias,
UPPER (a.VENDOR_TYPE_DISP) VENDOR_TYPE,
UPPER (A.WOMEN_OWNED_FLAG) WOMEN_OWNED,
UPPER (A.SMALL_BUSINESS_FLAG) SMALL_BUSINESS,
DECODE (A.MATCH_OPTION, 'P', 'Purchase Order', 'R', 'Receipt', NULL)
INVOICE_MATCH_OPTION,
UPPER (A.INVOICE_CURRENCY_CODE) INVOICE_CURRENCY,
UPPER (a.VAT_REGISTRATION_NUM) TAX_REGISTRATION_NUMBER,
UPPER (A.RECEIVING_ROUTING_NAME) RECEIPT_ROUTING,
DECODE (A.INVOICE_MATCHING_FLAG,
'NN', '2-Way',
'NY', '3-Way',
 'YY', '4-Way',
NULL)
MATCH_APPROVAL_LEVEL,
UPPER (A.ONE_TIME_FLAG) ONE_TIME_FLAG,
UPPER (A.CREATE_DEBIT_MEMO_FLAG) CREATE_DEBIT_MEMO_RTV,
A.ATTRIBUTE1,
DECODE (A.PAYMENT_METHOD_LOOKUP_CODE,
'CHECK', 'Check',
'CLEARING', 'Clearing',
'EFT', 'Electronic',
'WIRE', 'Wire',
NULL)
PAYMENT_METHOD,
UPPER (C.NAME) PAYMENT_TERMS,
DECODE (A.PAY_DATE_BASIS_LOOKUP_CODE,
'DISCOUNT', 'Discount',
'DUE', 'Due',
NULL)
PAY_DATE_BASIS_LOOKUP_CODE,
UPPER (A.PAYMENT_PRIORITY) PAYMENT_PRIORITY,
UPPER (A.PAY_GROUP_LOOKUP_CODE) PAY_GROUP_LOOKUP_CODE,
UPPER (A.INVOICE_CURRENCY_CODE) INVOICE_CURRENCY_CODE,
UPPER (A.PAYMENT_CURRENCY_CODE) PAYMENT_CURRENCY_CODE,
DECODE (A.TERMS_DATE_BASIS,
'Current', 'System',
'Goods Received', 'Goods Received',
'Invoice', 'Invoice',
'Invoice Received', 'Invoice Received',
NULL)
TERMS_DATE_BASIS,
UPPER (A.ORGANIZATION_TYPE_LOOKUP_CODE)
ORGANIZATION_TYPE_LOOKUP_CODE,
UPPER (A.INSPECTION_REQUIRED_FLAG) INSPECTION_REQUIRED_FLAG,
UPPER (A.RECEIPT_REQUIRED_FLAG) RECEIPT_REQUIRED_FLAG,
A.QTY_RCV_TOLERANCE,
DECODE (A.QTY_RCV_EXCEPTION_CODE,
'NONE', 'None',
'REJECT', 'Reject',
'WARNING', 'Warning',
NULL)
QTY_RCV_EXCEPTION_CODE,
UPPER (A.DAYS_EARLY_RECEIPT_ALLOWED) DAYS_EARLY_RECEIPT_ALLOWED,
UPPER (A.DAYS_LATE_RECEIPT_ALLOWED) DAYS_LATE_RECEIPT_ALLOWED,
A.vendor_id old_Ref_Vendor_id --,
-- a.vendor_id Old_Reference,
--pv.org_id
FROM apps.AP_VENDORS_V A,
apps.AP_TERMS C,
apps.po_vendor_sites_all pv,
apps.hr_operating_units hr
WHERE A.TERMS_ID = C.TERM_ID(+)
 AND a.END_DATE_ACTIVE IS NULL
AND a.vendor_id = pv.vendor_id
AND hr.SET_OF_BOOKS_ID = 5
--AND pv.org_id IN (1280, 1202, 1203, 1204, 1205)
AND hr.organization_id = pv.org_id
AND a.vendor_id IN (15685, 18484, 51547, 64242, 66436, 69531)

Select * from po_vendors where vendor_name in (Select distinct vendor_name from

xx_vendor_name1)

Select * from AP_VENDORS_V where vendor_name like '%NISHA%SHARMA'

--and a.VENDOR_TYPE_DISP not in ('Employee','EMPLOYEE', 'Ex-Employee','EX-

EMPLOYEE','EMPLOYEERM')
--and A.VENDOR_NUMBER in (Select distinct vendor_code from xx_vendor_code1)

Select DISTINCT VENDOR_TYPE_LOOKUP_CODE from po_vendors

Select organization_id,name from hr_operating_units where set_of_books_id=5

Posted by Elangovan Ragavan at 15:42 1 comment:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

Supplier_Contact
SELECT DISTINCT
b.org_id,
upper(hr.name) OU_NAME,
DECODE (b.org_id,
'1280', 'APIL-HO-10',
'1202', 'APIL - BPU UNIT - 20',
'1203', 'APIL - SEWA UNIT - 40',
'1204', 'APIL - BWN UNIT- 50',
'1205', 'APIL - SGU UNIT - 30',
hr.name)
OU_NAME,
b.vendor_site_id,
a.segment1 vendor_code,
/* Optional for data filter */
UPPER(DECODE (A.VENDOR_NAME,
'Withholding Tax Authority', 'WTA-RETENTION-A/C',
A.VENDOR_NAME))
C_VENDOR_NAME,
UPPER (B.VENDOR_SITE_CODE) C_SITE_CODE,
UPPER (C.TITLE) C_CONTACT_TITLE,
UPPER (C.FIRST_NAME) C_FIRST_NAME,
UPPER (C.LAST_NAME) C_LAST_NAME,
UPPER (B.EMAIL_ADDRESS) C_EMAIL,
 NULL URL,
C.AREA_CODE C_PHONE_AREA_CODE,
C.PHONE C_PHONE_NUMBER,
c.AREA_CODE C_PHONE_EXTENSION,
B.FAX_AREA_CODE C_FAX_AREA_CODE,
B.FAX C_FAX_NUMBER,
UPPER (C.ATTRIBUTE1) C_ATTRIBUTE1,
c.vendor_contact_id old_ref_contact_id
FROM apps.PO_VENDORS A,
apps.PO_VENDOR_SITES_ALL B,
apps.PO_VENDOR_CONTACTS C,
apps.hr_operating_units hr
WHERE A.VENDOR_ID = B.VENDOR_ID
AND B.VENDOR_SITE_ID = C.VENDOR_SITE_ID(+)
AND a.END_DATE_ACTIVE IS NULL
AND b.INACTIVE_DATE IS NULL
AND c.INACTIVE_DATE IS NULL
AND b.org_id = hr.organization_id
and hr.SET_OF_BOOKS_ID=5
AND a.vendor_id IN (15685, 18484, 51547, 64242, 66436, 69531)

--AND b.org_id IN (1280, 1202, 1203, 1204, 1205)

--and b.org_id = :P_org_id
-- AND hr.set_of_books_id = 5
--AND A.VENDOR_TYPE_LOOKUP_CODE NOT IN ('Ex-Employee', 'EX-
EMPLOYEE','Employee','EMPLOYEE','CONSULTANT','Consultant')

--AND a.segment1 IN ( SELECT VENDOR_code FROM xx_vendor_final_apil)

-- xx_vendor_mst_73)
ORDER BY TO_NUMBER (a.segment1), b.vendor_site_id
Posted by Elangovan Ragavan at 15:41 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

req_loc_tax_details
SELECT (SELECT poh.segment1
FROM po_requisition_headers_all poh
WHERE poh.requisition_header_id = po.requisition_header_id)
"PO NO",
(SELECT LINE_NUM
FROM po_requisition_lines_all
WHERE REQUISITION_LINE_ID = po.REQUISITION_LINE_ID)
"Line No",
tax.TAX_NAME,
po.TAX_TYPE,
tax.TAX_RATE,
po.TAX_AMOUNT
FROM JA_IN_REQN_TAX_LINES po, JA_IN_TAX_CODES tax
WHERE po.TAX_ID = tax.TAX_ID
AND po.REQUISITION_HEADER_ID IN
(SELECT DISTINCT
 prh.REQUISITION_HEADER_ID
from
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
org_organization_definitions ood
WHERE
prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id = prd.requisition_line_id
and ppf1.person_id = prh.preparer_id
and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
and ppf2.agent_id(+) = msi.buyer_id
and msi.inventory_item_id = prl.item_id
and msi.organization_id = prl.destination_organization_id
AND trunc(prh.creation_date) between '01-APR-2011' and '31-JUL-2011'
AND prh.org_id = ood.operating_unit
--AND prh.CLOSED_CODE NOT IN 'FINALLY CLOSED'
--AND prh.CLOSED_CODE NOT IN 'CLOSED'
--AND prh.closed_code NOT in 'CLOSE'
AND ood.set_of_books_id = 5)
ORDER BY 1
Posted by Elangovan Ragavan at 15:41 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

req_header
SELECT DISTINCT
(SELECT SEGMENT1 FROM PO_VENDORS WHERE VENDOR_ID=prl.VENDOR_ID)
Vendor_Code,
prh.requisition_header_id,
prl.requisition_line_id,
prh.segment1 REFERENCE_NUM,
prh.TYPE_LOOKUP_CODE TYPE,
prh.AUTHORIZATION_STATUS STATUS,
(SELECT full_name
FROM per_all_people_f
WHERE person_id = prh.preparer_id ) PREPARER,
prl.CURRENCY_CODE CURRENCY,
prl.DESTINATION_TYPE_CODE DESTINATION_TYPE,
ppf1.full_name "REQUESTOR",
(SELECT organization_name
FROM org_organization_definitions
WHERE organization_id = prl.destination_organization_id)
DESTINATION_ORGANIZATION,
(SELECT location_code
FROM hr_locations_all
WHERE location_id = prl.deliver_to_location_id) LOCATION,
prl.SOURCE_TYPE_CODE SOURCE,
 prl.SOURCE_ORGANIZATION_ID SOURCE_ORGANIZATION,
prl.SUGGESTED_VENDOR_NAME SUPPLIER,
prl.SUGGESTED_VENDOR_LOCATION SUPPLIER_SITE,
prl.SUGGESTED_VENDOR_CONTACT SUPPLIER_CONTACT,
(SELECT line_type
FROM po_line_types
WHERE line_type_id = prl.line_type_id) LINE_TYPE,
msi.segment1 Item_Code,
msi.description Item_Desc,
prl.UNIT_MEAS_LOOKUP_CODE UOM,
prl.QUANTITY,
prl.UNIT_PRICE PRICE,
prl.NEED_BY_DATE,
(SELECT concatenated_segments
FROM gl_code_combinations_kfv
WHERE code_combination_id = prd.CODE_COMBINATION_ID) CHARGE_ACCOUNT,
'' MULTIPLE_DISTRIBUTIONS,
prh.attribute1,
prh.attribute2,
prh.attribute3,
prh.attribute4,
prh.attribute5,
prl.RATE,
prl.RATE_TYPE,
prl.RATE_DATE
from
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
org_organization_definitions ood
WHERE
prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id = prd.requisition_line_id
and ppf1.person_id = prh.preparer_id
and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
and ppf2.agent_id(+) = msi.buyer_id
and msi.inventory_item_id = prl.item_id
and msi.organization_id = prl.destination_organization_id
AND trunc(prh.creation_date) between '01-Jul-2011' and '31-jul-2011'
AND prh.org_id = ood.operating_unit
AND (prh.CLOSED_CODE NOT IN 'FINALLY CLOSED'
AND prh.CLOSED_CODE NOT IN 'CLOSED'
AND prh.closed_code NOT in 'CLOSE'
OR prh.closed_code is null )
AND ood.set_of_books_id = 5
ORDER BY 1,2
Posted by Elangovan Ragavan at 15:40 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

req_distribution
SELECT DISTINCT
prh.REQUISITION_HEADER_ID,
prd.distribution_id,
prd.requisition_line_id,
PRH.segment1 D_REFERENCE_NUM,
prd.distribution_num D_DISTRIBUTION_NUM,
msi.segment1 D_ITEM,
(SELECT concatenated_segments
FROM gl_code_combinations_kfv
WHERE code_combination_id = prd.code_combination_id) D_CHARGE_ACCOUNT,
prd.req_line_quantity,
prd.attribute1,
prd.attribute2,
prd.attribute3,
prd.attribute4,
prd.attribute5
from
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
org_organization_definitions ood
WHERE
prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id = prd.requisition_line_id
and ppf1.person_id = prh.preparer_id
and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
and ppf2.agent_id(+) = msi.buyer_id
and msi.inventory_item_id = prl.item_id
and msi.organization_id = prl.destination_organization_id
AND trunc(prh.creation_date) between '01-Aug-2011' and '13-Aug-2011'
AND prh.org_id = ood.operating_unit
AND (prh.CLOSED_CODE NOT IN 'FINALLY CLOSED'
AND prh.CLOSED_CODE NOT IN 'CLOSED'
AND prh.closed_code NOT in 'CLOSE'
OR prh.closed_code is null )
AND ood.set_of_books_id = 5
ORDER BY 1,2
Posted by Elangovan Ragavan at 15:39 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

Receipt_Txn_Qry
/* Formatted on 10-08-2011 16:08:09 (QP5 v5.115.810.9015) */
SELECT (SELECT name
FROM hr_operating_units
WHERE organization_id = poh.org_id)
Operating_unit,
poh.vendor_id,
rt.SHIPMENT_HEADER_ID,
rt.SHIPMENT_LINE_ID,
rsh.RECEIPT_NUM L_REFERENCE,
rsh.SHIPMENT_NUM L_SHIPMENT_NUM,
rt.TRANSACTION_TYPE L_TRANSACTION_TYPE,
rt.TRANSACTION_DATE L_TRANSACTION_DATE,
poh.SEGMENT1 L_PO_NUMBER,
pol.LINE_NUM L_PO_LINE_NUM,
rt.QUANTITY L_QUANTITY,
rt.UNIT_OF_MEASURE L_UOM,
msi.segment1 L_ITEM_NUM,
rt.SUBINVENTORY L_SUBINVENTORY,
rt.DESTINATION_TYPE_CODE L_DESTINATION_TYPE,
pol.ITEM_DESCRIPTION L_ITEM_DESCRIPTION,
(SELECT ORGANIZATION_NAME
FROM org_organization_Definitions
WHERE organization_id = rsl.TO_ORGANIZATION_ID
AND set_of_books_id = 5)
L_TO_ORGANIZATION,
loc.LOCATION_CODE L_SHIP_TO_LOCATION,
rsh.SHIPMENT_NUM L_SHIPMENT_NUM
FROM rcv_transactions rt,
po_headers_all poh,
po_lines_all pol,
mtl_system_items_b msi,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
HR_LOCATIONS_ALL_TL loc,
po_line_locations_all poll
--, mtl_secondary_inventories inv
WHERE rt.organization_id IN (SELECT organization_id
FROM org_organization_Definitions
WHERE set_of_books_id = 5)
AND TRUNC (rt.TRANSACTION_DATE) BETWEEN '01-APR-2011' AND '10-APR-2011'
-- AND TRUNC (rt.Creation_Date) BETWEEN '01-apr-2011' AND '30-jun-2011'
AND rt.po_header_id = poh.po_header_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = rt.PO_LINE_ID
AND msi.INVENTORY_ITEM_ID = pol.item_id
AND rt.organization_id = msi.organization_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND poll.SHIP_TO_LOCATION_ID = loc.LOCATION_ID
AND rt.LOCATION_ID = poll.LINE_LOCATION_ID
ORDER BY TO_NUMBER (poh.segment1), rt.TRANSACTION_DATE
Posted by Elangovan Ragavan at 15:39 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

Receipt_Header_v1_0
/* Formatted on 12-08-2011 19:17:51 (QP5 v5.115.810.9015) */
SELECT DISTINCT
hou.name Operating_unit,
pov.vendor_id,
rt.SHIPMENT_HEADER_ID,
rt.SHIPMENT_LINE_ID,
pov.segment1 vendor_code,
TO_NUMBER (rsh.RECEIPT_NUM) H_REFERENCE,
RSH.SHIPMENT_NUM,
pov.VENDOR_NAME H_VENDOR_NAME,
RT.TRANSACTION_TYPE H_AUTO_TRX_CODE,
rsh.RECEIPT_SOURCE_CODE H_RECEIPT_SOURCE,
(SELECT ORGANIZATION_NAME
FROM APPS.org_organization_Definitions
WHERE organization_id = rsl.TO_ORGANIZATION_ID
AND set_of_books_id = 5)
H_SHIP_TO_ORGANIZATION,
rsh.EXPECTED_RECEIPT_DATE H_EXPECTED_RECEIPT_DATE,
rsh.WAYBILL_AIRBILL_NUM H_WAYBILL_AIRBILL,
rsh.BILL_OF_LADING H_BILL_OF_LADING
FROM APPS.rcv_transactions rt,
APPS.po_vendors pov,
APPS.rcv_shipment_lines rsl,
APPS.rcv_shipment_headers rsh,
APPS.po_headers_all poh,
hr_operating_units hou
WHERE rt.organization_id IN (SELECT organization_id
FROM APPS.org_organization_Definitions
WHERE set_of_books_id = 5)
AND TRUNC (rt.TRANSACTION_DATE) BETWEEN '01-APR-2011' AND '10-APR-2011'
--1AND '30-JUN-2011'
AND rt.vendor_id = pov.vendor_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND poh.po_header_id = rsl.po_header_id
AND poh.po_header_id = rt.po_header_id
AND hou.organization_id = poh.org_id
--and rt.TRANSACTION_TYPE NOT IN ('REJECT')
ORDER BY rt.SHIPMENT_HEADER_ID,
rt.SHIPMENT_LINE_ID,
TO_NUMBER (rsh.RECEIPT_NUM)
Posted by Elangovan Ragavan at 15:38 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

Receipt_Header
/* Formatted on 03-08-2011 14:14:08 (QP5 v5.115.810.9015) */
SELECT rsh.SHIPMENT_NUM H_REFERENCE,
pov.VENDOR_NAME H_VENDOR_NAME,
RT.TRANSACTION_TYPE H_AUTO_TRX_CODE,
rsh.RECEIPT_SOURCE_CODE H_RECEIPT_SOURCE,
(SELECT ORGANIZATION_NAME
FROM org_organization_Definitions
WHERE organization_id = rsl.TO_ORGANIZATION_ID
AND set_of_books_id = 5)
H_SHIP_TO_ORGANIZATION,
rsh.EXPECTED_RECEIPT_DATE H_EXPECTED_RECEIPT_DATE,
rsh.WAYBILL_AIRBILL_NUM H_WAYBILL_AIRBILL,
rsh.BILL_OF_LADING H_BILL_OF_LADING
FROM rcv_transactions rt,
po_vendors pov,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
po_headers_all poh
WHERE rt.organization_id IN (SELECT organization_id
FROM org_organization_Definitions
WHERE set_of_books_id = 5)
AND TRUNC (rt.Creation_Date) BETWEEN '01-APR-2011' AND '31-JUL-2011'
AND rt.vendor_id = pov.vendor_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND poh.po_header_id = rsl.po_header_id
AND poh.po_header_id = rt.po_header_id
ORDER BY TO_NUMBER (poh.segment1)

po_tax_with_item_name _quryin11i
SELECT DISTINCT poh.segment1 po_number,
mis.segment1 item_name,
TRUNC (poh.CREATION_DATE) po_date,
potax.TAX_TYPE,
jit.TAX_NAME,
potax.PRECEDENCE_1,
potax.PRECEDENCE_2,
potax.PRECEDENCE_3,
potax.PRECEDENCE_4,
potax.PRECEDENCE_5
FROM po_headers_all poh,
po_lines_all pol,
mtl_system_items_b mis,
JA_IN_PO_LINE_LOCATION_TAXES potax,
JA_IN_TAX_CODES jit
WHERE poh.PO_HEADER_ID = pol.PO_HEADER_ID
AND mis.inventory_item_id = pol.ITEM_ID
AND potax.PO_HEADER_ID = poh.PO_HEADER_ID
AND potax.TAX_ID = jit.TAX_ID
-- AND pol.po_line_id = potax.po_line_id
 AND TRUNC (poh.creation_date) BETWEEN '01-jan-2011'
AND '01-apr-2011'
Posted by Elangovan Ragavan at 15:34 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

po_requisition
SELECT DISTINCT
prh.segment1 "PR NUM",
trunc(prh.creation_date) "CREATED ON",
trunc(prl.creation_date) "Line Creation Date" ,
prl.line_num "Seq #",
msi.segment1 "Item Num",
prl.item_description "Description",
prl.quantity "Qty",
trunc(prl.need_by_date) "Required By",
ppf1.full_name "REQUESTOR",
ppf2.agent_name "BUYER"
from
po.po_requisition_headers_all prh,
po.po_requisition_lines_all prl,
apps.per_people_f ppf1,
(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,
po.po_req_distributions_all prd,
inv.mtl_system_items_b msi,
org_organization_definitions ood
WHERE
prh.requisition_header_id = prl.requisition_header_id
and prl.requisition_line_id = prd.requisition_line_id
and ppf1.person_id = prh.preparer_id
and prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_date
and ppf2.agent_id(+) = msi.buyer_id
and msi.inventory_item_id = prl.item_id
and msi.organization_id = prl.destination_organization_id
AND trunc(prh.creation_date) between '01-APR-2011' and '31-MAY-2011'
AND prh.org_id = ood.operating_unit
--AND prh.CLOSED_CODE NOT IN 'FINALLY CLOSED'
--AND prh.CLOSED_CODE NOT IN 'CLOSED'
--AND prh.closed_code NOT in 'CLOSE'
AND ood.set_of_books_id = 5
ORDER BY 1,2
Posted by Elangovan Ragavan at 15:33 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

PO_RECEIPTS_TAX_QRY
/* Formatted on 16-08-2011 19:50:37 (QP5 v5.115.810.9015) */
SELECT DISTINCT
hou.name Operating_unit,
TO_NUMBER (poh.segment1) PO_NO,
TO_NUMBER (rsh.RECEIPT_NUM) Receipt_No,
 pla.LINE_NUM,
pov1.vendor_name Fright_Vendor,
site.VENDOR_SITE_CODE,
(SELECT ood.organization_code
FROM org_organization_definitions ood
WHERE ood.organization_id = rsh.organization_id
AND ood.OPERATING_UNIT = hou.organization_id)
Organization_Code,
(SELECT ood.organization_name
FROM org_organization_definitions ood
WHERE ood.organization_id = rsh.organization_id
AND ood.OPERATING_UNIT = hou.organization_id)
Organization_Name,
rsl.TO_SUBINVENTORY Sub_Inventory,
jir.TAX_LINE_NO,
jir.TAX_NAME,
jir.TAX_RATE,
jir.TAX_TYPE,
jir.PRECEDENCE_1,
jir.PRECEDENCE_2,
jir.PRECEDENCE_3,
jir.PRECEDENCE_4,
jir.PRECEDENCE_5,
jir.CURRENCY,
jir.MODVAT_FLAG,
jir.THIRD_PARTY_FLAG,
jir.TAX_AMOUNT
FROM JA_IN_RECEIPT_TAX_LINES jir,
rcv_shipment_lines rsl,
po_vendors pov,
po_vendors pov1,
po_vendor_sites_all site,
po_headers_all poh,
po_lines_all pla,
rcv_shipment_headers rsh,
hr_operating_units hou --,
--org_organization_definitions ood
WHERE jir.SHIPMENT_LINE_ID = jir.SHIPMENT_LINE_ID
AND jir.SHIPMENT_HEADER_ID = rsl.SHIPMENT_HEADER_ID
AND pov.vendor_id = jir.vendor_id
AND site.vendor_site_id(+) = jir.vendor_site_id
AND poh.po_header_id = rsl.PO_HEADER_ID
AND poh.po_header_id = pla.po_header_id
AND rsl.po_line_id = pla.po_line_id
AND jir.vendor_id = pov1.vendor_id
AND rsh.SHIPMENT_HEADER_ID = jir.SHIPMENT_HEADER_ID
--AND jir.SHIPMENT_LINE_ID = 1454967
AND hou.organization_id(+) = poh.org_id
AND TRUNC (jir.CREATION_DATE) BETWEEN '01-APR-2011' --AND '05-APR-2011'
AND '31-jul-2011'
AND hou.set_of_books_id = 5
--and ood.set_of_books_id=5
ORDER BY TO_NUMBER (poh.segment1),
TO_NUMBER (rsh.RECEIPT_NUM),
pla.LINE_NUM,
jir.TAX_LINE_NO
Posted by Elangovan Ragavan at 15:33 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

PO_RECEIPT_TXN_Final_QRY
/* Formatted on 8/12/2011 11:02:52 PM (QP5 v5.115.810.9015) */
SELECT distinct hou.name Operating_unit,
ood.organization_name,
pov.vendor_id,
pov.segment1 vendor_code,
pov.vendor_name,
poh.po_header_id,
rt.SHIPMENT_HEADER_ID,
rt.SHIPMENT_LINE_ID,
rsh.RECEIPT_NUM L_REFERENCE,
rsh.SHIPMENT_NUM L_SHIPMENT_NUM,
rt.TRANSACTION_TYPE L_TRANSACTION_TYPE,
rt.TRANSACTION_DATE L_TRANSACTION_DATE,
poh.SEGMENT1 L_PO_NUMBER,
pol.LINE_NUM L_PO_LINE_NUM,
rt.QUANTITY L_QUANTITY,
rt.UNIT_OF_MEASURE L_UOM,
(SELECT segment1
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = pol.item_id
AND organization_id = ood.organization_id)
L_ITEM_NUM,
rt.SUBINVENTORY L_SUBINVENTORY,
rt.DESTINATION_TYPE_CODE L_DESTINATION_TYPE,
pol.ITEM_DESCRIPTION,
loc.LOCATION_CODE L_SHIP_TO_LOCATION,
rsh.SHIPMENT_NUM L_SHIPMENT_NUM
FROM rcv_transactions rt,
org_organization_definitions ood,
RCV_SHIPMENT_HEADERS RSH,
po_headers_all poh,
po_lines_all pol,
HR_LOCATIONS_ALL_TL loc,
po_line_locations_all poll,
hr_operating_units hou,
po_vendors pov
WHERE rt.ORGANIZATION_ID = ood.ORGANIZATION_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND rt.PO_HEADER_ID = poh.PO_HEADER_ID
AND poh.po_header_id = pol.po_header_id
AND rt.po_line_id = pol.po_line_id
 AND poll.PO_HEADER_ID = poh.PO_HEADER_ID
AND poll.po_line_id = pol.po_line_id
AND poll.SHIP_TO_LOCATION_ID = loc.LOCATION_ID
AND hou.organization_id = ood.operating_unit
AND pov.vendor_id = poh.vendor_id
AND ood.set_of_books_id = 5
AND TRUNC (rt.transaction_date) BETWEEN '01-Aug-2011'
AND '13-Aug-2011'
/*AND RT.TRANSACTION_ID IN ( SELECT MAX (TRANSACTION_ID)
FROM RCV_TRANSACTIONS
GROUP BY SHIPMENT_LINE_ID) */
ORDER BY TO_NUMBER (rsh.RECEIPT_NUM), pol.LINE_NUM
Posted by Elangovan Ragavan at 15:32 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

PO_RECEIPT_TAX_FINAL_QRY
/* Formatted on 17-08-2011 12:57:30 (QP5 v5.115.810.9015) */
SELECT hou.name Operting_unit,
(SELECT ood.organization_code
FROM org_organization_definitions ood
WHERE ood.organization_id = rsh.organization_id
AND ood.OPERATING_UNIT = hou.organization_id)
Organization_Code,
(SELECT ood.organization_name
FROM org_organization_definitions ood
WHERE ood.organization_id = rsh.organization_id
AND ood.OPERATING_UNIT = hou.organization_id)
Organization_Name,
rsh.SHIPMENT_HEADER_ID,
TO_NUMBER (pha.segment1) po_no,
TO_NUMBER (rsh.RECEIPT_NUM) Receipt_No,
plla.SHIPMENT_NUM,
jir.TAX_LINE_NO,
jir.TAX_NAME,
jir.TAX_RATE,
jir.TAX_TYPE,
jir.PRECEDENCE_1,
jir.PRECEDENCE_2,
jir.PRECEDENCE_3,
jir.PRECEDENCE_4,
jir.PRECEDENCE_5,
jir.CURRENCY,
jir.MODVAT_FLAG,
jir.THIRD_PARTY_FLAG,
jir.TAX_AMOUNT
FROM rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
ja.ja_in_receipt_tax_lines jir,
po_headers_all pha,
po_lines_all pla,
 po_line_locations_all plla,
hr_operating_units hou
WHERE rsh.shipment_header_id = rsl.shipment_header_id
AND jir.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_line_id = jir.shipment_line_id
AND pha.org_id = pla.org_id
AND pha.po_header_id = pla.po_header_id
AND pha.po_header_id = rsl.po_header_id
AND pha.org_id = pla.org_id
AND plla.org_id = pla.org_id
AND pla.po_header_id = plla.po_header_id
AND pla.po_line_id = plla.po_line_id
AND pla.po_line_id = rsl.po_line_id
AND rsl.po_line_location_id = plla.line_location_id
AND hou.organization_id = pha.org_id
AND TRUNC (jir.CREATION_DATE) BETWEEN '01-Aug-2011' --AND '05-APR-2011'
AND '13-Aug-2011'
AND HOU.SET_OF_BOOKS_ID=5
ORDER BY TO_NUMBER (pha.segment1),
TO_NUMBER (rsh.RECEIPT_NUM),
plla.SHIPMENT_NUM,
jir.TAX_LINE_NO
Posted by Elangovan Ragavan at 11:28 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

PO_RECEIPT_HEADER_Final_QRY
/* Formatted on 8/12/2011 10:38:36 PM (QP5 v5.115.810.9015) */
SELECT hou.name Operating_unit,
pov.vendor_id,
rt.SHIPMENT_HEADER_ID,
rt.SHIPMENT_LINE_ID,
pov.segment1 vendor_code,
pov.vendor_name,
poh.po_header_id,
TO_NUMBER (rsh.RECEIPT_NUM) H_REFERENCE,
RSH.SHIPMENT_NUM,
pov.VENDOR_NAME H_VENDOR_NAME,
poh.SEGMENT1 L_PO_NUMBER,
RT.TRANSACTION_TYPE H_AUTO_TRX_CODE,
rsh.RECEIPT_SOURCE_CODE H_RECEIPT_SOURCE,
ood.organization_name H_SHIP_TO_ORGANIZATION,
rsh.EXPECTED_RECEIPT_DATE H_EXPECTED_RECEIPT_DATE,
rsh.WAYBILL_AIRBILL_NUM H_WAYBILL_AIRBILL,
rsh.BILL_OF_LADING H_BILL_OF_LADING
FROM rcv_transactions rt,
org_organization_definitions ood,
RCV_SHIPMENT_HEADERS RSH,
po_headers_all poh,
po_lines_all pol,
po_vendors pov,
 HR_LOCATIONS_ALL_TL loc,
po_line_locations_all poll,
hr_operating_units hou,
rcv_shipment_lines rsl
WHERE rt.ORGANIZATION_ID = ood.ORGANIZATION_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND rt.PO_HEADER_ID = poh.PO_HEADER_ID
AND poh.po_header_id = pol.po_header_id
AND rt.po_line_id = pol.po_line_id
AND poll.PO_HEADER_ID = poh.PO_HEADER_ID
AND poll.po_line_id = pol.po_line_id
AND poll.SHIP_TO_LOCATION_ID = loc.LOCATION_ID
AND hou.organization_id = ood.operating_unit
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND ood.organization_id = rsl.TO_ORGANIZATION_ID
AND pov.vendor_id = poh.vendor_id
AND ood.set_of_books_id = 5
AND TRUNC (rt.transaction_date) BETWEEN '01-JUL-2011'
AND '31-JUL-2011'
/*AND RT.TRANSACTION_ID IN ( SELECT MAX (TRANSACTION_ID)
FROM RCV_TRANSACTIONS
GROUP BY SHIPMENT_LINE_ID) */
ORDER BY TO_NUMBER (rsh.RECEIPT_NUM)
Posted by Elangovan Ragavan at 11:27 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

po_query
SELECT distinct
decode(por.release_num,NULL, poh.segment1, poh.segment1 ||'-'|| por.release_num)
PO_Number_Release
, pol.line_num Line
, pov.vendor_name Vendor
, pol.item_revision Rev
, pol.item_description Description
, pll.shipment_num
, pod.distribution_num Distribution
, decode(plt.order_type_lookup_code, 'AMOUNT',NULL,pll.price_override) Unit_Price
, pll.promised_date Promised_Date
, pol.unit_meas_lookup_code Unit
, poh.po_header_id
, pol.po_line_id
, por.release_num
, poh.currency_code C_CURRENCY
, nvl(por.po_release_id,-1) release_id
FROM po_distributions_all pod
, mtl_system_items_b msi
, po_line_locations_all pll
, po_lines_all pol
, po_releases por
, po_headers_all poh
, po_vendors pov
, po_line_types plt
, org_organization_definitions ood
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id (+)
AND poh.vendor_id = pov.vendor_id (+)
AND pll.po_release_id = por.po_release_id (+)
AND pol.line_type_id = plt.line_type_id
AND trunc(poh.creation_date) between '01-APR-2011' and '31-JUL-2011'
AND poh.org_id = ood.operating_unit
AND ood.set_of_books_id = 5
Posted by Elangovan Ragavan at 11:26 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

PO_Loc_Tax_Final
/* Formatted on 11-08-2011 01:29:10 (QP5 v5.115.810.9015) */
Select * from XX_APIL_POLINETAX
DROP TABLE XX_APIL_POLINETAX;
CREATE TABLE XX_APIL_POLINETAX
AS
SELECT hou.name Operating_unit,
po.po_header_id,
po.PO_LINE_ID,
(SELECT vendor_name
FROM po_vendors
WHERE vendor_id = poh.vendor_id)
PO_Vendor_Name,
TO_NUMBER (poh.segment1) PO_NO,
pol.LINE_NUm PO_Line_No,
tax.tax_id,
tax.TAX_NAME,
po.TAX_TYPE,
tax.TAX_RATE,
po.TAX_LINE_NO,
po.PRECEDENCE_1,
po.PRECEDENCE_2,
po.PRECEDENCE_3,
po.PRECEDENCE_4,
po.PRECEDENCE_5,
po.MODVAT_FLAG,
po.TAX_AMOUNT,
pov.vendor_name
FROM JA_IN_PO_LINE_LOCATION_TAXES po,
JA_IN_TAX_CODES tax,
po_vendors pov,
po_headers_all poh,
hr_operating_units hou,
 po_lines_all pol
WHERE po.TAX_ID = tax.TAX_ID
AND pov.vendor_id = po.vendor_id
AND po.po_header_id = poh.po_header_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = po.po_line_id
AND TRUNC (poh.creation_date) BETWEEN '01-APR-2011'
AND '30-JUN-2011'
AND hou.ORGANIZATION_ID = poh.org_id
AND hou.set_of_books_id = 5
ORDER BY po.po_header_id,
po.PO_LINE_ID,
TO_NUMBER (poh.segment1),
pol.LINE_NUm,
po.TAX_LINE_NO

NEW
=====================
SELECT DISTINCT hou.name Operating_unit,
po.po_header_id,
po.PO_LINE_ID,
(SELECT vendor_name
FROM po_vendors
WHERE vendor_id = poh.vendor_id)
PO_Vendor_Name,
TO_NUMBER (poh.segment1) PO_NO,
mis.SEGMENT1 ITEM_NAME,
mis.DESCRIPTION ITEM_DESCRIPTION,
pol.LINE_NUm PO_Line_No,
tax.tax_id,
tax.TAX_NAME,
po.TAX_TYPE,
tax.TAX_RATE,
po.TAX_LINE_NO,
po.PRECEDENCE_1,
po.PRECEDENCE_2,
po.PRECEDENCE_3,
po.PRECEDENCE_4,
po.PRECEDENCE_5,
po.MODVAT_FLAG,
po.TAX_AMOUNT,
pov.vendor_name
FROM JA_IN_PO_LINE_LOCATION_TAXES po,
JA_IN_TAX_CODES tax,
po_vendors pov,
po_headers_all poh,
hr_operating_units hou,
po_lines_all pol,
mtl_system_items_b MIS
 WHERE po.TAX_ID = tax.TAX_ID
AND pov.vendor_id = po.vendor_id
AND po.po_header_id = poh.po_header_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = po.po_line_id
-- AND TRUNC (poh.creation_date) BETWEEN '01-APR-2011'
-- AND '30-JUN-2011'
AND hou.ORGANIZATION_ID = poh.org_id
AND MIS.INVENTORY_ITEM_ID = POL.ITEM_ID
AND POH.SEGMENT1 IN ('47402336')
AND hou.set_of_books_id = 5
ORDER BY po.po_header_id,
po.PO_LINE_ID,
TO_NUMBER (poh.segment1),
pol.LINE_NUm,
po.TAX_LINE_NO
Posted by Elangovan Ragavan at 11:26 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

po_loc_tax_details_v1
/* Formatted on 10-08-2011 15:17:46 (QP5 v5.115.810.9015) */
SELECT po.po_header_id,
po.PO_LINE_ID,
(SELECT TO_NUMBER (poh.segment1)
FROM po_headers_all poh
WHERE poh.po_header_id = po.po_header_id)
"PO NO",
(SELECT LINE_NUM
FROM po_lines_All
WHERE PO_LINE_ID = po.PO_LINE_ID)
"Line No",
tax.tax_id,
tax.TAX_NAME,
po.TAX_TYPE,
tax.TAX_RATE,
po.TAX_LINE_NO,
po.PRECEDENCE_1,
po.PRECEDENCE_2,
po.PRECEDENCE_3,
po.PRECEDENCE_4,
po.PRECEDENCE_5,
po.TAX_AMOUNT
FROM JA_IN_PO_LINE_LOCATION_TAXES po, JA_IN_TAX_CODES tax
WHERE po.TAX_ID = tax.TAX_ID
AND po.PO_HEADER_ID IN
(SELECT DISTINCT pha.po_header_id
FROM PO_HEADERS_ALL PHA,
PO_VENDORS PV,
PO_VENDOR_SITES_ALL PVS,
HR_LOCATIONS H1,
 PER_ALL_PEOPLE_F ppf,
org_organization_definitions ood
WHERE PV.VENDOR_ID(+) = PHA.VENDOR_ID
AND PVS.VENDOR_SITE_ID(+) = PHA.VENDOR_SITE_ID
AND H1.LOCATION_ID(+) = PHA.SHIP_TO_LOCATION_ID
AND ppf.person_id(+) = PHA.AGENT_ID
AND pha.org_id = ood.operating_unit
AND TRUNC (pha.creation_date) BETWEEN '01-JUL-2011'
AND '31-JUL-2011'
AND ood.set_of_books_id = 5)
ORDER BY po.PO_HEADER_ID, po.PO_LINE_ID, po.TAX_LINE_NO
Posted by Elangovan Ragavan at 11:25 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

po_loc_tax_details_v1.1
/* Formatted on 11-08-2011 01:20:16 (QP5 v5.115.810.9015) */
SELECT hou.name Operating_unit,
po.po_header_id,
po.PO_LINE_ID,
(SELECT vendor_name
FROM po_vendors
WHERE vendor_id = poh.vendor_id)
PO_Vendor_Name,
TO_NUMBER (poh.segment1) "PO NO",
pol.LINE_NUm "Line No",
tax.tax_id,
tax.TAX_NAME,
po.TAX_TYPE,
tax.TAX_RATE,
po.TAX_LINE_NO,
po.PRECEDENCE_1,
po.PRECEDENCE_2,
po.PRECEDENCE_3,
po.PRECEDENCE_4,
po.PRECEDENCE_5,
po.MODVAT_FLAG,
po.TAX_AMOUNT,
pov.vendor_name
FROM JA_IN_PO_LINE_LOCATION_TAXES po,
JA_IN_TAX_CODES tax,
po_vendors pov,
po_headers_all poh,
hr_operating_units hou,
po_lines_all pol
WHERE po.TAX_ID = tax.TAX_ID
AND pov.vendor_id = po.vendor_id
AND po.po_header_id = poh.po_header_id
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = po.po_line_id
AND TRUNC (poh.creation_date) BETWEEN '01-JUL-2011'
 AND '31-JUL-2011'
AND hou.ORGANIZATION_ID = poh.org_id
AND hou.set_of_books_id = 5
ORDER BY po.po_header_id,
po.PO_LINE_ID,
TO_NUMBER (poh.segment1),
pol.LINE_NUm,
po.TAX_LINE_NO
Posted by Elangovan Ragavan at 11:24 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

po_lines_with_PR_NUMBER
SELECT DISTINCT
prh.segment1 PR_NO,
pl.po_header_id,
pl.po_line_id,
(SELECT segment1
FROM po_headers_all poh
WHERE poh.po_header_id = pl.po_header_id
AND poh.org_id = pl.org_id)
L_REFERENCE_NUM,
pl.line_num,
pll.shipment_num,
plt.line_type,
msi.segment1 Item_Code,
msi.DESCRIPTION Item_Desc,
pl.unit_meas_lookup_code uom_code,
pl.quantity,
pl.unit_price,
pll.promised_date,
ood.organization_code ship_to_organization_code,
hloc.location_code l_ship_to_location,
pll.invoice_close_tolerance inv_cl_toler,
pll.receive_close_tolerance rec_cl_toler,
pl.attribute_category l_attribute_category,
pl.attribute1 l_attribute1,
pl.attribute2 l_attribute2,
pl.attribute3 l_attribute3,
pl.attribute4 l_attribute4,
pl.attribute5 l_attribute5,
pl.attribute6 l_attribute6,
pl.attribute7 l_attribute7,
pl.attribute8 l_attribute8,
pl.attribute9 l_attribute9,
pl.attribute10 l_attribute10,
pl.attribute11 l_attribute11,
pl.attribute12 l_attribute12,
pl.attribute13 l_attribute13,
pl.attribute14 l_attribute14,
pl.attribute15 l_attribute15
FROM po_lines_all pl,
po_line_types plt,
mtl_system_items_b msi,
po_line_locations_all pll,
org_organization_definitions ood,
hr_locations_all hloc,
po_requisition_headers_All prh,
po_requisition_lines_all prl,
po_req_distributions_All prd,
po_distributions_all pod
WHERE pl.line_type_id = plt.line_type_id
AND pl.item_id = msi.inventory_item_id
AND pl.po_line_id = pll.po_line_id
AND pl.org_id = pll.org_id
AND hloc.location_id(+) = pll.ship_to_location_id
AND ood.organization_id(+) = pll.ship_to_organization_id
AND pl.org_id = ood.operating_unit
AND prh.REQUISITION_HEADER_ID = prl.REQUISITION_HEADER_ID
AND prl.REQUISITION_LINE_ID = prd.REQUISITION_LINE_ID
and pod.REQ_DISTRIBUTION_ID=prd.DISTRIBUTION_ID
AND ood.set_of_books_id = 5
AND TRUNC (pl.creation_date) BETWEEN '01-APR-2011' AND '31-jul-2011'
Posted by Elangovan Ragavan at 11:24 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

po_line
/* Formatted on 08-08-2011 15:57:51 (QP5 v5.115.810.9015) */
SELECT DISTINCT
pl.po_header_id,
pl.po_line_id,
(SELECT segment1
FROM po_headers_all poh
WHERE poh.po_header_id = pl.po_header_id
AND poh.org_id = pl.org_id)
L_REFERENCE_NUM,
pl.line_num,
pll.shipment_num,
plt.line_type,
msi.segment1 Item_Code,
msi.DESCRIPTION Item_Desc,
pl.ITEM_DESCRIPTION,
pl.unit_meas_lookup_code uom_code,
pl.quantity,
pl.unit_price,
pll.promised_date,
ood.organization_code ship_to_organization_code,
hloc.location_code l_ship_to_location,
pll.invoice_close_tolerance inv_cl_toler,
pll.receive_close_tolerance rec_cl_toler,
pl.attribute_category l_attribute_category,
 pl.attribute1 l_attribute1,
pl.attribute2 l_attribute2,
pl.attribute3 l_attribute3,
pl.attribute4 l_attribute4,
pl.attribute5 l_attribute5,
pl.attribute6 l_attribute6,
pl.attribute7 l_attribute7,
pl.attribute8 l_attribute8,
pl.attribute9 l_attribute9,
pl.attribute10 l_attribute10,
pl.attribute11 l_attribute11,
pl.attribute12 l_attribute12,
pl.attribute13 l_attribute13,
pl.attribute14 l_attribute14,
pl.attribute15 l_attribute15,
pl.po_line_id,
pl.po_header_id
FROM po_lines_all pl,
po_line_types plt,
mtl_system_items_b msi,
po_line_locations_all pll,
org_organization_definitions ood,
hr_locations_all hloc
WHERE pl.line_type_id = plt.line_type_id
AND pl.item_id = msi.inventory_item_id(+)
AND pl.po_line_id = pll.po_line_id
AND pl.org_id = pll.org_id
AND hloc.location_id(+) = pll.ship_to_location_id
AND ood.organization_id(+) = pll.ship_to_organization_id
AND pl.org_id = ood.operating_unit
AND ood.set_of_books_id = 5
AND TRUNC (pl.creation_date) BETWEEN '01-jul-2011' AND '31-jul-2011'

Posted by Elangovan Ragavan at 11:23 1 comment:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

po_header_v1
SELECT DISTINCT poh.po_header_id,
pov.segment1 vendor_no,
poh.segment1 REFERENCE_NUM,
poh.AUTHORIZATION_STATUS PROCESS_CODE,
poh.TYPE_LOOKUP_CODE DOCUMENT_TYPE_CODE,
poh.currency_code CURRENCY_CODE,
(SELECT full_name
FROM per_all_people_f
WHERE person_id = poh.agent_id)
AGENT_NAME,
pov.vendor_name,
(SELECT vendor_site_code
FROM po_vendor_sites_all
 WHERE vendor_site_id = poh.vendor_site_id)
VENDOR_SITE_CODE,
(SELECT location_code
FROM hr_locations
WHERE location_id = poh.ship_to_location_id)
SHIP_TO_LOCATION,
(SELECT location_code
FROM hr_locations
WHERE location_id = poh.bill_to_location_id)
BILL_TO_LOCATION,
poh.ATTRIBUTE_CATEGORY,
poh.ATTRIBUTE1,
poh.ATTRIBUTE2,
poh.ATTRIBUTE3,
poh.ATTRIBUTE4,
poh.ATTRIBUTE5,
poh.ATTRIBUTE6,
poh.ATTRIBUTE7,
poh.ATTRIBUTE8,
poh.ATTRIBUTE9,
poh.ATTRIBUTE10,
poh.ATTRIBUTE11,
poh.ATTRIBUTE12,
poh.ATTRIBUTE13,
poh.ATTRIBUTE14,
poh.ATTRIBUTE15,
poh.Rate EXCHANGE_RATE,
poh.rate_type EXCHANGE_RATE_TYPE,
poh.rate_date EXCHANGE_RATE_DATE,
'' BATCH_ID,
ood.operating_unit
FROM po_distributions_all pod,
mtl_system_items_b msi,
po_line_locations_all pll,
po_lines_all pol,
po_releases por,
po_headers_all poh,
po_vendors pov,
po_line_types plt,
org_organization_definitions ood
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id(+)
AND poh.vendor_id = pov.vendor_id(+)
AND pll.po_release_id = por.po_release_id(+)
AND pol.line_type_id = plt.line_type_id
AND TRUNC (poh.creation_date) BETWEEN '01-JUL-2011'
AND '31-JUL-2011'
AND poh.org_id = ood.operating_unit
AND ood.set_of_books_id = 5
 new

SELECT DISTINCT poh.po_header_id,

pov.segment1 vendor_no,
poh.segment1 REFERENCE_NUM,
poh.AUTHORIZATION_STATUS PROCESS_CODE,
poh.TYPE_LOOKUP_CODE DOCUMENT_TYPE_CODE,
poh.currency_code CURRENCY_CODE,
(SELECT full_name
FROM per_all_people_f
WHERE person_id = poh.agent_id)
AGENT_NAME,
pov.vendor_name,
(SELECT vendor_site_code
FROM po_vendor_sites_all
WHERE vendor_site_id = poh.vendor_site_id)
VENDOR_SITE_CODE,
(SELECT location_code
FROM hr_locations
WHERE location_id = poh.ship_to_location_id)
SHIP_TO_LOCATION,
(SELECT location_code
FROM hr_locations
WHERE location_id = poh.bill_to_location_id)
BILL_TO_LOCATION,
(SELECT NAME
FROM AP_TERMS
WHERE TERM_ID = poh.TERMS_ID)
payment_term,
POH.FOB_LOOKUP_CODE,
POH.BILL_TO_LOCATION_ID,
POH.SHIP_TO_LOCATION_ID,
POL.QUANTITY,
POL.UNIT_PRICE,
PLL.PROMISED_DATE,
poh.ATTRIBUTE_CATEGORY,
poh.ATTRIBUTE1,
poh.ATTRIBUTE2,
poh.ATTRIBUTE3,
poh.ATTRIBUTE4,
poh.ATTRIBUTE5,
poh.ATTRIBUTE6,
poh.ATTRIBUTE7,
poh.ATTRIBUTE8,
poh.ATTRIBUTE9,
poh.ATTRIBUTE10,
poh.ATTRIBUTE11,
poh.ATTRIBUTE12,
poh.ATTRIBUTE13,
 poh.ATTRIBUTE14,
poh.ATTRIBUTE15,
poh.Rate EXCHANGE_RATE,
poh.rate_type EXCHANGE_RATE_TYPE,
poh.rate_date EXCHANGE_RATE_DATE,
'' BATCH_ID,
ood.operating_unit
FROM po_distributions_all pod,
mtl_system_items_b msi,
po_line_locations_all pll,
po_lines_all pol,
po_releases por,
po_headers_all poh,
po_vendors pov,
po_line_types plt,
org_organization_definitions ood
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id(+)
AND poh.vendor_id = pov.vendor_id(+)
AND pll.po_release_id = por.po_release_id(+)
AND pol.line_type_id = plt.line_type_id
-- AND TRUNC (poh.creation_date) BETWEEN '01-JUL-2011'
-- AND '31-JUL-2011'
AND poh.org_id = ood.operating_unit
AND ood.set_of_books_id = 5
AND POH.SEGMENT1 IN ('42405822',
'42405843',
'42405897',
'42405952',
'42406032',
'42406077',
'42406104',
'42406112',
'42406182',
'42406269',
'44402482',
'44402511',
'47402318',
'47402322',
'47402336',
'47402414',
'47402422',
'43404694',
'43404721',
'43404734',
'43404792',
'43404966',
'43405019',
'43405021',
'43405036')

Posted by Elangovan Ragavan at 11:22 1 comment:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

po_distribution_v1
SELECT distinct
poh.po_header_id,
pol.po_line_id,
poh.segment1 D_REFERENCE_NUM
, pol.line_num D_LINE_NUM
, pll.shipment_num D_SHIP_NUM
, pod.distribution_num
, pod.quantity_ordered QUANTITY_ORDERED
,(SELECT concatenated_segments
FROM gl_code_combinations_kfv
WHERE code_combination_id = pod.code_combination_id) CHARGE_ACCOUNT,
pod.attribute_category D_ATTRIBUTE_CATEGORY,
pod.attribute1 D_ATTRIBUTE1,
pod.attribute2 D_ATTRIBUTE2,
pod.attribute3 D_ATTRIBUTE3,
pod.attribute4 D_ATTRIBUTE4,
pod.attribute5 D_ATTRIBUTE5,
pod.attribute6 D_ATTRIBUTE6,
pod.attribute7 D_ATTRIBUTE7,
pod.attribute8 D_ATTRIBUTE8,
pod.attribute9 D_ATTRIBUTE9,
pod.attribute10 D_ATTRIBUTE10,
pod.attribute11 D_ATTRIBUTE11,
pod.attribute12 D_ATTRIBUTE12,
pod.attribute13 D_ATTRIBUTE13,
pod.attribute14 D_ATTRIBUTE14,
pod.attribute15 D_ATTRIBUTE15
FROM po_distributions_all pod
, mtl_system_items_b msi
, po_line_locations_all pll
, po_lines_all pol
, po_releases por
, po_headers_all poh
, po_vendors pov
, po_line_types plt
, org_organization_definitions ood
WHERE poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id (+)
AND poh.vendor_id = pov.vendor_id (+)
AND pll.po_release_id = por.po_release_id (+)
AND pol.line_type_id = plt.line_type_id
AND trunc(poh.creation_date) between '01-JUL-2011' and '31-JUL-2011'
AND poh.org_id = ood.operating_unit
AND ood.set_of_books_id = 5

Posted by Elangovan Ragavan at 11:20 1 comment:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

TUESDAY, 25 OCTOBER 2011

OPEN_PO_TAX_DTLS_QRY
sELECT (SELECT poh.segment1
FROM po_headers_all poh
WHERE poh.po_header_id = po.po_header_id)
"PO NO",
(SELECT LINE_NUM
FROM po_lines_All
WHERE PO_LINE_ID = po.PO_LINE_ID)
"Line No",
tax.TAX_NAME,
po.TAX_TYPE,
tax.TAX_RATE,
po.TAX_AMOUNT
FROM JA_IN_PO_LINE_LOCATION_TAXES po, JA_IN_TAX_CODES tax
WHERE po.TAX_ID = tax.TAX_ID
AND po.PO_HEADER_ID IN (272618, 274687, 275004, 285765)
order by po.PO_HEADER_ID

sELECT (SELECT poh.segment1

FROM po_headers_all poh
WHERE poh.po_header_id = po.po_header_id)
"PO NO",
(SELECT LINE_NUM
FROM po_lines_All
WHERE PO_LINE_ID = po.PO_LINE_ID)
Line_No,
tax.TAX_NAME,
po.TAX_TYPE,
tax.TAX_RATE,
po.TAX_AMOUNT
FROM JA_IN_PO_LINE_LOCATION_TAXES po, JA_IN_TAX_CODES tax
WHERE po.TAX_ID = tax.TAX_ID
AND po.PO_HEADER_ID IN (155050,163369,173100,193758,247641)
order by po.PO_HEADER_ID

sELECT (SELECT poh.segment1

FROM po_headers_all poh
WHERE poh.po_header_id = po.po_header_id)
 "PO NO",
(SELECT LINE_NUM
FROM po_lines_All
WHERE PO_LINE_ID = po.PO_LINE_ID)
Line_No,
tax.TAX_NAME,
po.TAX_TYPE,
tax.TAX_RATE,
po.TAX_AMOUNT
FROM JA_IN_PO_LINE_LOCATION_TAXES po, JA_IN_TAX_CODES tax
WHERE po.TAX_ID = tax.TAX_ID
AND po.PO_HEADER_ID IN

(263661,266317,266354,267277,269224,269319,269320,270552,273381,273536,280116,2822
46,
282576,282577,290615,291394,296359,296360,296770,297580,298025)
order by po.PO_HEADER_ID

sELECT (SELECT poh.segment1

FROM po_headers_all poh
WHERE poh.po_header_id = po.po_header_id)
"PO NO",
(SELECT LINE_NUM
FROM po_lines_All
WHERE PO_LINE_ID = po.PO_LINE_ID)
Line_No,
tax.TAX_NAME,
po.TAX_TYPE,
tax.TAX_RATE,
po.TAX_AMOUNT
FROM JA_IN_PO_LINE_LOCATION_TAXES po, JA_IN_TAX_CODES tax
WHERE po.TAX_ID = tax.TAX_ID
AND po.PO_HEADER_ID IN
(

290046,321422,321423,321828,322652,323710,324547,325963,327577,328512,332321,33240
5,333966,334350,334481,334565,
334902,335664,337180,337301,338082,338110,338529,338646,338998,338999,339393,33939
4,339729,340731,341150,341173,341198)
order by po.PO_HEADER_ID

Posted by Elangovan Ragavan at 12:21 1 comment:

Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

Ar_Receipt_Dist_Qry

/* Formatted on 14-08-2011 21:48:44 (QP5 v5.115.810.9015) */

SELECT /* PURPOSE: THE VIEW AR_MISC_CASH_DISTRIBUTIONS_V IS USED IN THE
*/
 /* MISC CASH DISTRIBUTIONS WINDOW OF THE RECEIPT GATEWAY */
DISTINCT
HOU.NAME OPERATING_UNIT,
MCD.MISC_CASH_DISTRIBUTION_ID MISC_CASH_DISTRIBUTION_ID,
MCD.CASH_RECEIPT_ID CASH_RECEIPT_ID,
CR.RECEIPT_NUMBER,
MCD.CODE_COMBINATION_ID CODE_COMBINATION_ID,
KFV.CONCATENATED_SEGMENTS ACCT_DESC,
MCD.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
MCD.PERCENT PERCENT,
DECODE (SIGN (MCD.AMOUNT),
1,
SIGN (MCD.AMOUNT) * DIST.AMOUNT_CR,
-1,
SIGN (MCD.AMOUNT) * DIST.AMOUNT_DR,
0,
SIGN (MCD.AMOUNT) * DIST.AMOUNT_CR)
AMOUNT,
DECODE (SIGN (MCD.ACCTD_AMOUNT),
1,
SIGN (MCD.ACCTD_AMOUNT) * DIST.ACCTD_AMOUNT_CR,
-1,
SIGN (MCD.ACCTD_AMOUNT) * DIST.ACCTD_AMOUNT_DR,
0,
SIGN (MCD.ACCTD_AMOUNT) * DIST.ACCTD_AMOUNT_CR)
ACCTD_AMOUNT,
CR.CURRENCY_CODE CURRENCY_CODE,
CR.EXCHANGE_RATE EXCHANGE_RATE,
MCD.COMMENTS COMMENTS,
MCD.GL_POSTED_DATE GL_POSTED_DATE,
MCD.GL_DATE GL_DATE,
MCD.APPLY_DATE APPLY_DATE,
MCD.POSTING_CONTROL_ID POSTING_CONTROL_ID,
MCD.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
MCD.REVERSAL_GL_DATE
FROM AR_MISC_CASH_DISTRIBUTIONS_ALL MCD,
AR_CASH_RECEIPTS_ALL CR,
AR_DISTRIBUTIONS_ALL DIST,
AR_SYSTEM_PARAMETERS_ALL ASP,
GL_CODE_COMBINATIONS_KFV KFV,
HR_OPERATING_UNITS HOU
WHERE CR.CASH_RECEIPT_ID = MCD.CASH_RECEIPT_ID
AND MCD.MISC_CASH_DISTRIBUTION_ID = DIST.SOURCE_ID
AND KFV.CODE_COMBINATION_ID = MCD.CODE_COMBINATION_ID
 AND CR.ORG_ID=HOU.ORGANIZATION_ID
AND (DIST.SOURCE_TYPE = 'MISCCASH')
AND MCD.REVERSAl_GL_DATE IS NULL
AND ASP.ACCOUNTING_METHOD = 'ACCRUAL'
-- and cr.STATUS not in ('REV','NSF')
AND TRUNC (CR.RECEIPT_DATE) BETWEEN '01-Apr-2011' AND '31-Jul-2011'
AND CR.SET_OF_BOOKS_ID = 5
UNION ALL
SELECT DISTINCT
HOU.NAME OPERATING_UNIT,
MCD.MISC_CASH_DISTRIBUTION_ID MISC_CASH_DISTRIBUTION_ID,
MCD.CASH_RECEIPT_ID CASH_RECEIPT_ID,
CR.RECEIPT_NUMBER,
MCD.CODE_COMBINATION_ID CODE_COMBINATION_ID,
KFV.CONCATENATED_SEGMENTS ACCT_DESC,
MCD.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
MCD.PERCENT PERCENT,
MCD.AMOUNT,
MCD.ACCTD_AMOUNT,
CR.CURRENCY_CODE CURRENCY_CODE,
CR.EXCHANGE_RATE EXCHANGE_RATE,
MCD.COMMENTS COMMENTS,
MCD.GL_POSTED_DATE GL_POSTED_DATE,
MCD.GL_DATE GL_DATE,
MCD.APPLY_DATE APPLY_DATE,
MCD.POSTING_CONTROL_ID POSTING_CONTROL_ID,
MCD.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
MCD.REVERSAL_GL_DATE
FROM AR_MISC_CASH_DISTRIBUTIONS_ALL MCD,
AR_CASH_RECEIPTS_ALL CR,
ar_system_parameters_ALL asp,
GL_CODE_COMBINATIONS_KFV KFV,
HR_OPERATING_UNITS HOU
WHERE CR.CASH_RECEIPT_ID = MCD.CASH_RECEIPT_ID
AND KFV.CODE_COMBINATION_ID = MCD.CODE_COMBINATION_ID
AND CR.ORG_ID=HOU.ORGANIZATION_ID
AND TRUNC (CR.RECEIPT_DATE) BETWEEN '01-Apr-2011' AND '31-Jul-2011'
AND cr.SET_OF_BOOKS_ID = 5
--and cr.STATUS not in ('REV', 'NSF')
AND asp.accounting_method = 'MISC';
AR_LINE

/* Formatted on 09-08-2011 16:25:39 (QP5 v5.115.810.9015) */

SELECT DISTINCT (SELECT name
FROM hr_operating_units
 WHERE organization_id = rctl.org_id)
Operating_unit,
RCTA.TRX_NUMBER,
rctl.customer_trx_id L_LINE_HEADER_ID,
rctl.line_number L_LINE_NUM,
rctl.description L_DESCRIPTION,
rctl.QUANTITY_INVOICED L_QUANTITY,
rctl.unit_selling_price L_UNIT_PRICE,
RCTL.EXTENDED_AMOUNT Txn_Amount,
rctl.attribute_category L_ATTR_CONTEXT,
rctl.attribute1 L_ATTRIBUTE1,
rctl.attribute2 L_ATTRIBUTE2,
rctl.attribute3 L_ATTRIBUTE3,
rctl.attribute4 L_ATTRIBUTE4,
rctl.attribute5 L_ATTRIBUTE5,
rctl.memo_line_id L_MEMO_LINE
FROM ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rct,
ra_customer_trx_all rcta
WHERE rctl.customer_trx_line_id = rct.customer_trx_line_id
AND rctl.customer_trx_id = rcta.customer_trx_id
AND rcta.customer_trx_id = rct.customer_trx_id
--AND rcta.customer_trx_id = jai.customer_trx_id(+)
--AND jai.customer_trx_id = jline.customer_trx_id
AND rcta.org_id = rctl.org_id
AND rctl.LINE_TYPE = 'LINE'
AND TRUNC (rcta.TRX_DATE) BETWEEN '01-AUG-2011' AND '13-AUG-2011'
AND rcta.SET_OF_BOOKS_ID = 5
Posted by Elangovan Ragavan at 12:10 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

AR_HEADERS_QOERY
select * from ja_in_ra_customer_trx_lines
select * from JA_IN_RA_CUSTOMER_TRX where set_of_books_id=5
and trunc(creation_date) BETWEEN '01-APR-2011' AND '31-MAY-2011'
select distinct ra.CUSTOMER_ID ,ra.CUSTOMER_NUMBER, rcta.customer_trx_id
H_TRX_HEADER_ID,
bat.name H_BATCH_SOURCE,
rcta.trx_number H_TRX_NUMBER,
--jai.TRX_NUMBER,
decode(typ.TYPE,'INV','Invoice','CM','Credit Memo','DM','Debit
Memo','DEP','Deposit','CB','Chargeback')
H_INVOICE_CLASS,
rcta.invoice_currency_code,
ra.customer_name h_bill_to_customer,
typ.name H_TRX_TYPE,
(SELECT name
FROM hr_operating_units
WHERE organization_id = rct.org_id) H_ORIG_REFERENCE,
rcta.trx_date H_INVOICE_DATE,
 ter.name H_TERMS_CODE,
rct.gl_date H_GL_DATE,
(SELECT location
FROM hz_cust_site_uses_all
WHERE site_use_id = rcta.bill_to_site_use_id) H_BILL_TO_SITE,
rcta.exchange_date,
rcta.exchange_rate,
rcta.exchange_rate_type,
(SELECT organization_name
from org_organization_definitions
where organization_id = jai.organization_id) H_ORGANIZATION,
(SELECT description
from hr_locations_all
where location_id = jai.location_id )H_LOCATION,
rcta.attribute5
from ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rct,
ra_customer_trx_all rcta,
ar_batch_sources_all bat,
ja_in_ra_customer_trx jai,
ra_cust_trx_types_ALL typ,
fnd_lookups fnd,
ra_customers ra,
ra_terms ter
where rctl.customer_trx_line_id=rct.customer_trx_line_id
and rctl.customer_trx_id=rcta.customer_trx_id
and rcta.customer_trx_id=rct.customer_trx_id
AND rcta.customer_trx_id = jai.customer_trx_id(+)
AND rcta.batch_source_id = bat.batch_source_id(+)
AND rcta.BILL_TO_CUSTOMER_ID = ra.CUSTOMER_ID(+)
and rcta.CUST_TRX_TYPE_ID = typ.CUST_TRX_TYPE_ID
and rcta.term_id = ter.term_id(+)
AND typ.TYPE = fnd.LOOKUP_CODE
--AND fnd.LOOKUP_TYPE = 'JEBE_AR_OF_TRANS_TYPE'
AND TRUNC(rcta.TRX_DATE) BETWEEN '01-AUG-2011' AND '13-AUG-2011'
AND rcta.SET_OF_BOOKS_ID = 5
order by 1

AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID

AND CTT.TYPE <> 'BR'
Select * from fnd_lookups where LOOKUP_TYPE = 'JEBE_AR_OF_TRANS_TYPE'
Posted by Elangovan Ragavan at 12:08 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

INVOICE_ISSUE_query
SELECT DISTINCT
mmt.TRANSACTION_ID,
MTT.TRANSACTION_TYPE_NAME TRANSACTION_TYPE,
mmt.SOURCE_CODE SOURCE_CODE,
mmt.TRANSACTION_SOURCE_NAME TRANSACTION_SOURCE,
 OOD.ORGANIZATION_CODE ORGANIZATION_CODE,
MMT.SUBINVENTORY_CODE SUBINVENTORY_CODE,
msi.segment1 INVENTORY_ITEM,
mmt.REVISION REVISION,
glcc.CONCATENATED_SEGMENTS DISTRIBUTION_ACCT,
mmt.TRANSACTION_QUANTITY TRANSACTION_QTY,
mmt.TRANSACTION_UOM TRANSACTION_UOM,
mmt.TRANSACTION_DATE TRANSACTION_DATE,
'' LINE,
mmt.TRANSACTION_COST TRANSACTION_COST,
'' FM_SERIAL_NUMBER,
'' TO_SERIAL_NUMBER,
'' ENTITY_TYPE,
mmt.FLOW_SCHEDULE FLOW_SCHEDULE,
'' SCHEDULE_TYPE
FROM mtl_material_transactions mmt,
mtl_transaction_types mtt,
ORG_ORGANIZATION_DEFINITIONS OOD,
MTL_SYSTEM_ITEMS_B MSI,
GL_CODE_COMBINATIONS_KFV GLCC
WHERE MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND OOD.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND GLCC.CODE_COMBINATION_ID=MMT.DISTRIBUTION_ACCOUNT_ID
AND TRUNC (TRANSACTION_DATE) BETWEEN '01-JUL-2011' AND '31-JUL-2011'
AND OOD.SET_OF_BOOKS_ID = 5
ORDER BY OOD.ORGANIZATION_CODE
Posted by Elangovan Ragavan at 12:03 1 comment:
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest
Labels: Data Extraction

WEDNESDAY, 19 OCTOBER 2011

Recipet information 11i & R12

SELECT DISTINCT rsh.RECEIPT_NUM,
rt.TRANSACTION_TYPE,
rt.CURRENCY_CODE,
rsh.VENDOR_SITE_ID,
rsh.VENDOR_ID,
rsh.SHIPPED_DATE,
-- aia.INVOICE_ID,
aia.INVOICE_NUM,
aia.AMOUNT_PAID,
aia.DOC_SEQUENCE_VALUE,
aia.DOC_CATEGORY_CODE,
aia.INVOICE_AMOUNT,
aia.INVOICE_DATE,
aia.INVOICE_TYPE_LOOKUP_CODE,
aia.DESCRIPTION,
aia.AMOUNT_APPLICABLE_TO_DISCOUNT,
aia.ACCTS_PAY_CODE_COMBINATION_ID,
 aia.PAYMENT_METHOD_LOOKUP_CODE
FROM rcv_transactions rt,
rcv_shipment_headers rsh,
ap_invoice_distributions_all aida,
ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_payment_schedules_all apsa,
ap_checks_all aca
WHERE rsh.SHIPMENT_HEADER_ID = rt.SHIPMENT_HEADER_ID
AND rt.TRANSACTION_ID = aida.RCV_TRANSACTION_ID
AND aia.INVOICE_ID = aida.INVOICE_ID
--and aia.INVOICE_ID=aila.INVOICE_ID
AND aia.INVOICE_ID = aipa.INVOICE_ID(+)
AND aia.INVOICE_ID = apsa.INVOICE_ID
AND aca.CHECK_ID(+) = aipa.CHECK_ID
AND rsh.RECEIPT_NUM IN
('436001948',
'436001949',
'436001951',
'436001950',
'436001950')