CREATE OR REPLACE PACKAGE XX_UOM_PKG AS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE INSERT_DATA_STG;
PROCEDURE INSERT_COLUMN_STG;
PROCEDURE VALIDATE_UOM_STG;
PROCEDURE XX_UOM_IMPORT;
END XX_UOM_PKG;
/
CREATE OR REPLACE PACKAGE BODY XX_UOM_PKG AS
L_ERROR_MESSAGE VARCHAR2(1000);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)
AS
BEGIN
INSERT_DATA_STG;
INSERT_COLUMN_STG;
VALIDATE_UOM_STG;
XX_UOM_IMPORT;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20000,SQLERRM);
END;
PROCEDURE INSERT_DATA_STG
IS
CURSOR CUR_STG
IS
SELECT UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
BASE_UOM_FLAG,
UNIT_OF_MEASURE_TL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
DISABLE_DATE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE='IN';
BEGIN
DELETE XX_UOM_CONV_STG;
COMMIT;
FOR REC_CUR_STG IN CUR_STG
LOOP
INSERT INTO XX_UOM_CONV_STG
(UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
BASE_UOM_FLAG,
UNIT_OF_MEASURE_TL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
DISABLE_DATE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_FLAG)
VALUES
(REC_CUR_STG.UNIT_OF_MEASURE,
REC_CUR_STG.UOM_CODE,
REC_CUR_STG.UOM_CLASS,
REC_CUR_STG.BASE_UOM_FLAG,
REC_CUR_STG.UNIT_OF_MEASURE_TL,
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
'-1',
NULL,
REC_CUR_STG.DESCRIPTION,
REC_CUR_STG.LANGUAGE,
REC_CUR_STG.SOURCE_LANG,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N');
END LOOP;
END INSERT_DATA_STG ;
PROCEDURE INSERT_COLUMN_STG IS
BEGIN
INSERT INTO XX_UOM_CONV_STG
(UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
BASE_UOM_FLAG,
UNIT_OF_MEASURE_TL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
DISABLE_DATE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_FLAG)
VALUES
('POISE',
'PO',
'VISCOSITY',
'N',
'POISE',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
'-1',
NULL,
'POISE',
'US',
'US',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N');
COMMIT;
END INSERT_COLUMN_STG;
PROCEDURE VALIDATE_UOM_STG
IS
L_UNIT_OF_MEASURE MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
L_UOM_CODE MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
L_UOM_CLASS MTL_UOM_CLASSES.UOM_CLASS%TYPE;
L_UNIT_OF_MEASURE_TL MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE_TL%TYPE;
L_LANGUAGE FND_LANGUAGES.LANGUAGE_CODE%TYPE;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_VAL_UOM
IS
SELECT A.*,ROWID
FROM XX_UOM_CONV_STG A
WHERE STATUS_FLAG='N';
BEGIN
FOR REC_CUR_VAL_UOM IN CUR_VAL_UOM
LOOP
BEGIN
SELECT UNIT_OF_MEASURE
INTO L_UNIT_OF_MEASURE
FROM MTL_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = REC_CUR_VAL_UOM.UNIT_OF_MEASURE;
UPDATE XX_UOM_CONV_STG
SET UNIT_OF_MEASURE=L_UNIT_OF_MEASURE
WHERE ROWID= REC_CUR_VAL_UOM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || ' ERROR UNIT OF MEASURE';
END;
BEGIN
SELECT LANGUAGE_CODE
INTO L_LANGUAGE
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE=REC_CUR_VAL_UOM.LANGUAGE;
UPDATE XX_UOM_CONV_STG
SET LANGUAGE=L_LANGUAGE
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'LANGUAGE DOES NOT EXIST';
END;
BEGIN
SELECT UOM_CLASS
INTO L_UOM_CLASS
FROM MTL_UOM_CLASSES
WHERE UOM_CLASS = REC_CUR_VAL_UOM.UOM_CLASS;
UPDATE XX_UOM_CONV_STG
SET UOM_CLASS=L_UOM_CLASS
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'UNIT OF MEASURE CLASS DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_UOM_CONV_STG
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
ELSE
UPDATE XX_UOM_CONV_STG
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
END IF;
COMMIT;
END LOOP;
UPDATE XX_UOM_CONV_STG SET UNIT_OF_MEASURE='SRI',UOM_CODE='SRI';
COMMIT;
END VALIDATE_UOM_STG;
PROCEDURE XX_UOM_IMPORT
IS
L_ROW_ID VARCHAR2(200):= NULL;
L_DISABLE_DATE DATE:=NULL;
L_ATTRIBUTE_CATEGORY VARCHAR2(200):= NULL;
L_ATTRIBUTE1 VARCHAR2(200):= NULL;
L_ATTRIBUTE2 VARCHAR2(200):= NULL;
L_ATTRIBUTE3 VARCHAR2(200):= NULL;
L_ATTRIBUTE4 VARCHAR2(200):= NULL;
L_ATTRIBUTE5 VARCHAR2(200):= NULL;
L_ATTRIBUTE6 VARCHAR2(200):= NULL;
L_ATTRIBUTE7 VARCHAR2(200):= NULL;
L_ATTRIBUTE8 VARCHAR2(200):= NULL;
L_ATTRIBUTE9 VARCHAR2(200):= NULL;
L_ATTRIBUTE10 VARCHAR2(200):= NULL;
L_ATTRIBUTE11 VARCHAR2(200):= NULL;
L_ATTRIBUTE12 VARCHAR2(200):= NULL;
L_ATTRIBUTE13 VARCHAR2(200):= NULL;
L_ATTRIBUTE14 VARCHAR2(200):= NULL;
L_ATTRIBUTE15 VARCHAR2(200):= NULL;
L_REQUEST_ID NUMBER:= 1;
L_PROGRAM_ID NUMBER:= 1;
L_PROGRAM_APPLICATION_ID NUMBER:= 1;
L_PROGRAM_UPDATE_DATE DATE:= SYSDATE;
L_LAST_UPDATE_LOGIN NUMBER:=1;
L_COUNT1 NUMBER;
L_COUNT2 NUMBER;
L_COUNT3 NUMBER;
CURSOR CUR_IMP_UOM IS SELECT * FROM XX_UOM_CONV_STG;
BEGIN
FOR REC_CUR_IMP_UOM IN CUR_IMP_UOM LOOP
SELECT COUNT(*) INTO L_COUNT1 FROM MTL_UOM_CLASSES_TL WHERE UOM_CLASS=REC_CUR_IMP_UOM.UOM_CLASS ;
SELECT COUNT(*) INTO L_COUNT2 FROM MTL_UNITS_OF_MEASURE_TL WHERE UNIT_OF_MEASURE=REC_CUR_IMP_UOM.UNIT_OF_MEASURE;
SELECT COUNT(*) INTO L_COUNT3 FROM MTL_UNITS_OF_MEASURE_TL WHERE UOM_CLASS=REC_CUR_IMP_UOM.UOM_CLASS AND BASE_UOM_FLAG='Y';
IF L_COUNT3 =0 OR (L_COUNT3 IS NOT NULL AND REC_CUR_IMP_UOM.BASE_UOM_FLAG!='Y') THEN
IF L_COUNT1 IS NOT NULL AND L_COUNT2 = 0 THEN
MTL_UNITS_OF_MEASURE_TL_PKG.INSERT_ROW (L_ROW_ID,
REC_CUR_IMP_UOM.UNIT_OF_MEASURE,
REC_CUR_IMP_UOM.UNIT_OF_MEASURE_TL,
L_ATTRIBUTE_CATEGORY,
L_ATTRIBUTE1,
L_ATTRIBUTE2,
L_ATTRIBUTE3,
L_ATTRIBUTE4,
L_ATTRIBUTE5,
L_ATTRIBUTE6,
L_ATTRIBUTE7,
L_ATTRIBUTE8,
L_ATTRIBUTE9,
L_ATTRIBUTE10,
L_ATTRIBUTE11,
L_ATTRIBUTE12,
L_ATTRIBUTE13,
L_ATTRIBUTE14,
L_ATTRIBUTE15,
L_REQUEST_ID,
L_DISABLE_DATE,
REC_CUR_IMP_UOM.BASE_UOM_FLAG,
REC_CUR_IMP_UOM.UOM_CODE,
REC_CUR_IMP_UOM.UOM_CLASS,
REC_CUR_IMP_UOM.DESCRIPTION,
SYSDATE,
'1318',
SYSDATE,
'1318',
'-1',
L_PROGRAM_APPLICATION_ID,
L_PROGRAM_ID,
L_PROGRAM_UPDATE_DATE,
REC_CUR_IMP_UOM.LANGUAGE );
ELSIF L_COUNT1 =0 AND L_COUNT2 IS NOT NULL THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,REC_CUR_IMP_UOM.UOM_CLASS||' UOM CLASS DOES NOT EXIST
AND ' || REC_CUR_IMP_UOM.UNIT_OF_MEASURE||' UNIT OF MEASURE ALREADY EXISTS');
ELSIF L_COUNT1 =0 AND L_COUNT2 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'UOM CLASS DOES NOT EXIST');
ELSIF L_COUNT1 IS NOT NULL AND L_COUNT2 IS NOT NULL THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'UNIT OF MEASURE ALREADY EXISTS');
END IF;
ELSE
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'BASE UNIT OF MEASURE ALREADY EXISTS');
END IF;
END LOOP;
COMMIT;
END XX_UOM_IMPORT;
END XX_UOM_PKG;
/
=====================================================
CONCURRENT PROGRAM FOR UOM CONVERSION:
=============================================
MAIN PRG OF CONVERSION(UOM)
==========================================================
Tuesday, April 22, 2008
To Capture the Information in DFF in purchasing
TO POPULATE THE VALUES IN DESCRIPTIVE FLEX FIELDS:
=====================================================
1)COPY THE CUSTOM.PLL AND KEEP IT IN THE LOCAL DIRECTORY
2)OPEN THE FORM BUILDER
3)OPEN THE APPLICATIONS AND CREATE THE DFF THROUGH APPLICATION DEVELOPER RESPONSIBILITY
4)NOTE THE FORM NAME AND BLOCK NAME
5)WRITE THE FOLLOWING CODE BY GIVING APPROPRIATE FORM NAME AND BLOCK NAME.
6)WRITE A CUSTOM PACKAGE AND WRITE A PROCEDURE INTO IT.
7)RETRIVE THE VALUES FROM THE TABLES THROUGH A SELECT STATEMENT AND STORE IT IN VARIABLES
8)USE "COPY" FUNCTION AND THEN POPULATE INTO THE REQUIRED FIELDS.
CODE WHICH IS TO BE WRITTEN IN CUSTOM.PLL:
============================================
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
param_to_pass1 varchar2(255);
-- param_to_pass2 varchar2(255);
begin
-- Zoom event opens a new session of a form and
-- passes parameter values to the new session. The parameters
-- already exist in the form being opened.
if (form_name = 'RCVRCERC' and block_name = 'RCV_TRANSACTION') then
if (event_name = 'WHEN-NEW-BLOCK-INSTANCE') then
param_to_pass1 := name_in('HEADER.RECEIVER');
PO_FLEX.P1(param_to_pass1);
END IF;
END IF;
end event;
CODE WHICH IS TO BE WRITTEN IN CUSTOM PACKAGE:
==============================================
PACKAGE BODY PO_FLEX IS
PROCEDURE P1 (param_to_pass1 IN VARCHAR2) IS
B NUMBER(10);
C varchar2(30) ;
D NUMBER(10);
E VARCHAR2(30);
BEGIN
C := name_in('HEADER.RECEIVER');
MESSAGE('ABC');
MESSAGE(' ');
SELECT PAPF.PERSON_ID,PAPF.EMPLOYEE_NUMBER,PAPF.FULL_NAME INTO B,D,E
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.FULL_NAME = C;
COPY(B,'RCV_TRANSACTION.ATTRIBUTE4');
COPY(D,'RCV_TRANSACTION.ATTRIBUTE4');
COPY(E,'RCV_TRANSACTION.ATTRIBUTE4');
END P1;
END PO_FLEX;
=====================================================
1)COPY THE CUSTOM.PLL AND KEEP IT IN THE LOCAL DIRECTORY
2)OPEN THE FORM BUILDER
3)OPEN THE APPLICATIONS AND CREATE THE DFF THROUGH APPLICATION DEVELOPER RESPONSIBILITY
4)NOTE THE FORM NAME AND BLOCK NAME
5)WRITE THE FOLLOWING CODE BY GIVING APPROPRIATE FORM NAME AND BLOCK NAME.
6)WRITE A CUSTOM PACKAGE AND WRITE A PROCEDURE INTO IT.
7)RETRIVE THE VALUES FROM THE TABLES THROUGH A SELECT STATEMENT AND STORE IT IN VARIABLES
8)USE "COPY" FUNCTION AND THEN POPULATE INTO THE REQUIRED FIELDS.
CODE WHICH IS TO BE WRITTEN IN CUSTOM.PLL:
============================================
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
param_to_pass1 varchar2(255);
-- param_to_pass2 varchar2(255);
begin
-- Zoom event opens a new session of a form and
-- passes parameter values to the new session. The parameters
-- already exist in the form being opened.
if (form_name = 'RCVRCERC' and block_name = 'RCV_TRANSACTION') then
if (event_name = 'WHEN-NEW-BLOCK-INSTANCE') then
param_to_pass1 := name_in('HEADER.RECEIVER');
PO_FLEX.P1(param_to_pass1);
END IF;
END IF;
end event;
CODE WHICH IS TO BE WRITTEN IN CUSTOM PACKAGE:
==============================================
PACKAGE BODY PO_FLEX IS
PROCEDURE P1 (param_to_pass1 IN VARCHAR2) IS
B NUMBER(10);
C varchar2(30) ;
D NUMBER(10);
E VARCHAR2(30);
BEGIN
C := name_in('HEADER.RECEIVER');
MESSAGE('ABC');
MESSAGE(' ');
SELECT PAPF.PERSON_ID,PAPF.EMPLOYEE_NUMBER,PAPF.FULL_NAME INTO B,D,E
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.FULL_NAME = C;
COPY(B,'RCV_TRANSACTION.ATTRIBUTE4');
COPY(D,'RCV_TRANSACTION.ATTRIBUTE4');
COPY(E,'RCV_TRANSACTION.ATTRIBUTE4');
END P1;
END PO_FLEX;
RTV_By Org,by Buyer(xml)
Report Query
select a.quantity,a.po_line_id,a.employee_id,b.full_name,a.organization_id, d.organization_code, d.organization_name,c.item_description,a.vendor_id, a.vendor_site_id,e.vendor_name from rcv_transactions a, po_buyers_all_v b ,po_lines_all c, org_organization_definitions d, ap_vendors_v e where a.po_line_id=c.po_line_id and a.employee_id=b.employee_id and a.vendor_id = e.vendor_id and a.organization_id=d.organization_id and a.transaction_type='RETURN TO VENDOR' &p_where_clause
Report Triggers
1) After Parameter Form Trigger :
function AfterPForm return boolean is
begin
if :P_Organization is null and :P_BuyerName is null then
:p_where_clause:='';
elsif :P_BuyerName is null and :P_Organization is not null then
:p_where_clause:= 'and d.organization_id = :P_Organization';
elsif :P_BuyerName is not null and :P_Organization is null then
:p_where_clause:= 'and b.full_name=:P_BuyerName';
else
:p_where_clause:= 'and d.organization_id = :P_Organization and b.full_name=:P_BuyerName';
end If;
return (TRUE);
end;
select a.quantity,a.po_line_id,a.employee_id,b.full_name,a.organization_id, d.organization_code, d.organization_name,c.item_description,a.vendor_id, a.vendor_site_id,e.vendor_name from rcv_transactions a, po_buyers_all_v b ,po_lines_all c, org_organization_definitions d, ap_vendors_v e where a.po_line_id=c.po_line_id and a.employee_id=b.employee_id and a.vendor_id = e.vendor_id and a.organization_id=d.organization_id and a.transaction_type='RETURN TO VENDOR' &p_where_clause
Report Triggers
1) After Parameter Form Trigger :
function AfterPForm return boolean is
begin
if :P_Organization is null and :P_BuyerName is null then
:p_where_clause:='';
elsif :P_BuyerName is null and :P_Organization is not null then
:p_where_clause:= 'and d.organization_id = :P_Organization';
elsif :P_BuyerName is not null and :P_Organization is null then
:p_where_clause:= 'and b.full_name=:P_BuyerName';
else
:p_where_clause:= 'and d.organization_id = :P_Organization and b.full_name=:P_BuyerName';
end If;
return (TRUE);
end;
Report on order details shipped (X) days
select a.ORDER_NUMBER,a.ORDERED_DATE,a.ORDER_CATEGORY_CODE,a.ORDER_TYPE_ID,a.TRANSACTIONAL_CURR_CODE,
b.LINE_NUMBER,b.ORDERED_ITEM,b.PRICING_QUANTITY_UOM,b.SHIPPED_QUANTITY,b.SHIPMENT_NUMBER,b.REQUEST_DATE,
b.ACTUAL_SHIPMENT_DATE,c.ORGANIZATION_NAME SHIP_FROM_ORG,round(b.actual_shipment_date-b.request_date) days
from
oe_order_headers_all a,
oe_order_lines_all b,
org_organization_definitions c
where a.header_id=b.header_id and
b.SHIP_FROM_ORG_ID=c.ORGANIZATION_ID and
round(b.actual_shipment_date-b.request_date)>:ENTER_THE_DAYS
b.LINE_NUMBER,b.ORDERED_ITEM,b.PRICING_QUANTITY_UOM,b.SHIPPED_QUANTITY,b.SHIPMENT_NUMBER,b.REQUEST_DATE,
b.ACTUAL_SHIPMENT_DATE,c.ORGANIZATION_NAME SHIP_FROM_ORG,round(b.actual_shipment_date-b.request_date) days
from
oe_order_headers_all a,
oe_order_lines_all b,
org_organization_definitions c
where a.header_id=b.header_id and
b.SHIP_FROM_ORG_ID=c.ORGANIZATION_ID and
round(b.actual_shipment_date-b.request_date)>:ENTER_THE_DAYS
Report on item categories
SELECT PV.VENDOR_NAME,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1 ITEM,
MSI.DESCRIPTION,
(MB.SEGMENT1||'-'||MB.SEGMENT2||'-'||MB.SEGMENT3) CATEGORY,
MB.CATEGORY_ID,
MT.DESCRIPTION CATEGORYNAME,
MSI.ORGANIZATION_ID,
MCSB.CATEGORY_SET_ID,
MCST.CATEGORY_SET_NAME,
MCSB.STRUCTURE_ID,
PASL.VENDOR_BUSINESS_TYPE
FROM PO_VENDORS PV,
MTL_SYSTEM_ITEMS_B MSI,
PO_APPROVED_SUPPLIER_LIST PASL,
MTL_CATEGORY_SET_VALID_CATS MCSV,
MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORIES_B MB,
MTL_CATEGORIES_TL MT,
MTL_CATEGORY_SETS_B MCSB,
MTL_CATEGORY_SETS_TL MCST
WHERE PV.VENDOR_ID=PASL.VENDOR_ID
AND PASL.ITEM_ID=MSI.INVENTORY_ITEM_ID
AND PASL.OWNING_ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MB.CATEGORY_ID=MIC.CATEGORY_ID
AND MT.CATEGORY_ID=MIC.CATEGORY_ID
AND MCSV.CATEGORY_ID=MIC.CATEGORY_ID
AND MCSV.CATEGORY_SET_ID=MIC.CATEGORY_SET_ID
AND MCSV.CATEGORY_SET_ID=MCSB.CATEGORY_SET_ID
AND MCSV.CATEGORY_SET_ID=MCST.CATEGORY_SET_ID
AND VENDOR_NAME=:VENDORNAME
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1 ITEM,
MSI.DESCRIPTION,
(MB.SEGMENT1||'-'||MB.SEGMENT2||'-'||MB.SEGMENT3) CATEGORY,
MB.CATEGORY_ID,
MT.DESCRIPTION CATEGORYNAME,
MSI.ORGANIZATION_ID,
MCSB.CATEGORY_SET_ID,
MCST.CATEGORY_SET_NAME,
MCSB.STRUCTURE_ID,
PASL.VENDOR_BUSINESS_TYPE
FROM PO_VENDORS PV,
MTL_SYSTEM_ITEMS_B MSI,
PO_APPROVED_SUPPLIER_LIST PASL,
MTL_CATEGORY_SET_VALID_CATS MCSV,
MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORIES_B MB,
MTL_CATEGORIES_TL MT,
MTL_CATEGORY_SETS_B MCSB,
MTL_CATEGORY_SETS_TL MCST
WHERE PV.VENDOR_ID=PASL.VENDOR_ID
AND PASL.ITEM_ID=MSI.INVENTORY_ITEM_ID
AND PASL.OWNING_ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MB.CATEGORY_ID=MIC.CATEGORY_ID
AND MT.CATEGORY_ID=MIC.CATEGORY_ID
AND MCSV.CATEGORY_ID=MIC.CATEGORY_ID
AND MCSV.CATEGORY_SET_ID=MIC.CATEGORY_SET_ID
AND MCSV.CATEGORY_SET_ID=MCSB.CATEGORY_SET_ID
AND MCSV.CATEGORY_SET_ID=MCST.CATEGORY_SET_ID
AND VENDOR_NAME=:VENDORNAME
Report On Inv Balances(xml)
select msi.segment1,mmt.inventory_item_id,mmt.subinventory_code,ood.organization_name,mmt.organization_id,
nvl(sum(decode(substr(mmt.primary_quantity,1,1),'-',-primary_quantity,null)),0) issue,
nvl(sum(decode(substr(mmt.primary_quantity,1,1),'-',null,primary_quantity)),0) receipt
from mtl_material_transactions mmt, mtl_system_items msi, org_organization_definitions ood
where
mmt.organization_id=msi.organization_id and
mmt.inventory_item_id=msi.inventory_item_id and
mmt.organization_id=ood.organization_id and
mmt.organization_id=:org and
mmt.transaction_date between :d1 and :d2
group by msi.segment1, mmt.inventory_item_id,mmt.subinventory_code,ood.organization_name,mmt.organization_id
order by msi.segment1
nvl(sum(decode(substr(mmt.primary_quantity,1,1),'-',-primary_quantity,null)),0) issue,
nvl(sum(decode(substr(mmt.primary_quantity,1,1),'-',null,primary_quantity)),0) receipt
from mtl_material_transactions mmt, mtl_system_items msi, org_organization_definitions ood
where
mmt.organization_id=msi.organization_id and
mmt.inventory_item_id=msi.inventory_item_id and
mmt.organization_id=ood.organization_id and
mmt.organization_id=:org and
mmt.transaction_date between :d1 and :d2
group by msi.segment1, mmt.inventory_item_id,mmt.subinventory_code,ood.organization_name,mmt.organization_id
order by msi.segment1
PO Outstand Balance
query1:
=======
SELECT PHA.PO_HEADER_ID,
PHA.SEGMENT1 "PO Number",
SUM(PLA.UNIT_PRICE*PLA.QUANTITY) "PO Total"
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA
WHERE
PHA.PO_HEADER_ID IN( PLA.PO_HEADER_ID)
AND PHA.TYPE_LOOKUP_CODE ='STANDARD'
GROUP BY
PHA.PO_HEADER_ID,PHA.SEGMENT1
query2:
=======
SELECT
DISTINCT(AIA.INVOICE_NUM) "Invoice Number",
AIA.INVOICE_DATE "Invoice Date",
AIA.INVOICE_AMOUNT "Invoice Amount",
AIA.AMOUNT_PAID "Amount Paid",
PDA.PO_HEADER_ID
FROM
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
PO_DISTRIBUTIONS_ALL PDA
WHERE
AIA.INVOICE_ID IN (AIDA.INVOICE_ID)
AND
AIDA.PO_DISTRIBUTION_ID IN(PDA.PO_DISTRIBUTION_ID);
=======
SELECT PHA.PO_HEADER_ID,
PHA.SEGMENT1 "PO Number",
SUM(PLA.UNIT_PRICE*PLA.QUANTITY) "PO Total"
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA
WHERE
PHA.PO_HEADER_ID IN( PLA.PO_HEADER_ID)
AND PHA.TYPE_LOOKUP_CODE ='STANDARD'
GROUP BY
PHA.PO_HEADER_ID,PHA.SEGMENT1
query2:
=======
SELECT
DISTINCT(AIA.INVOICE_NUM) "Invoice Number",
AIA.INVOICE_DATE "Invoice Date",
AIA.INVOICE_AMOUNT "Invoice Amount",
AIA.AMOUNT_PAID "Amount Paid",
PDA.PO_HEADER_ID
FROM
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
PO_DISTRIBUTIONS_ALL PDA
WHERE
AIA.INVOICE_ID IN (AIDA.INVOICE_ID)
AND
AIDA.PO_DISTRIBUTION_ID IN(PDA.PO_DISTRIBUTION_ID);
Subscribe to:
Posts (Atom)