SELECT AIA.INVOICE_NUM,
AIA.INVOICE_TYPE_LOOKUP_CODE,
AIA.INVOICE_AMOUNT,
AIA.INVOICE_DATE,
APS.VENDOR_NAME SUPPLIER_NAME,
ASSA.VENDOR_SITE_CODE,
AIA.INVOICE_CURRENCY_CODE,
AIA.PAYMENT_CURRENCY_CODE,
AIA.PAYMENT_METHOD_CODE,
AILA.LINE_NUMBER,
HOU.NAME,
GL.NAME,
AP.NAME,
AILA.AMOUNT,
AILA.LINE_TYPE_LOOKUP_CODE,
APS.VENDOR_ID
FROM AP_INVOICES_ALL AIA,
AP_INVOICE_LINES_ALL AILA,
AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL ASSA,
HR_OPERATING_UNITS HOU,
GL_LEDGERS GL,
AP_TERMS AP
WHERE AIA.INVOICE_ID=AILA.INVOICE_ID AND
APS.VENDOR_ID=ASSA.VENDOR_ID AND
AP.TERM_ID=AIA.TERMS_ID AND
AIA.VENDOR_ID=APS.VENDOR_ID AND
AILA.ORG_ID=HOU.ORGANIZATION_ID AND
AIA.INVOICE_NUM='DELL4'AND
ASSA.VENDOR_SITE_ID=AIA.VENDOR_SITE_ID AND
AIA.SET_OF_BOOKS_ID=GL.LEDGER_ID
--------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------
PREPAYMENT:
SELECT*FROM AP_INVOICES_ALL WHERE INVOICE_NUM='DELL3'
SELECT*FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=212216
SELECT*FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=212216
SELECT*fROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOICE_ID=212216
SELECT*fROM AP_HOLDS_ALL WHERE INVOICE_ID=212216
SELECT*fROM AP_CHECKS_ALL WHERE CHECK_NUMBER='6606' AND BANK_ACCOUNT_NAME='BofA-204'
SELECT*fROM AP_INVOICE_PAYMENTS_ALL WHERE CHECK_ID=77968
--------------------------------
SELECT*FROM AP_INVOICES_ALL WHERE INVOICE_NUM='DELL4'
SELECT*FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=212221
SELECT*FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=212221
SELECT*fROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOICE_ID=212221
SELECT*fROM AP_HOLDS_ALL WHERE INVOICE_ID=212221
SELECT*fROM AP_CHECKS_ALL WHERE CHECK_NUMBER='6610' AND BANK_ACCOUNT_NAME='BofA-204'
SELECT*fROM AP_INVOICE_PAYMENTS_ALL WHERE CHECK_ID=77972
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7063
SELECT*FROM PO_HEADERS_ALL WHERE SEGMENT1='7063' AND TYPE_LOOKUP_CODE='STANDARD'
SELECT*FROM PO_LINES_ALL WHERE PO_HEADER_ID=111936
SELECT*FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=174767
SELECT*fROM PO_DISTRIBUTIONS_ALL WHERE LINE_LOCATION_ID=266082
24233
SELECT*FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM='24233'
SELECT*FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID=4822539
SELECT*FROM RCV_TRANSACTIONS WHERE SHIPMENT_LINE_ID=4814036
6600
-----------------------
SELECT*FROM AP_INVOICES_ALL WHERE INVOICE_NUM='DELL2'
SELECT*FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=212205
SELECT*FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=212205
SELECT*fROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOICE_ID=212205
SELECT*fROM AP_HOLDS_ALL WHERE INVOICE_ID=212205
SELECT*fROM AP_CHECKS_ALL WHERE CHECK_NUMBER='6600' AND BANK_ACCOUNT_NAME='BofA-204'
SELECT*fROM AP_INVOICE_PAYMENTS_ALL WHERE CHECK_ID=77949
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PAYMENT TERMS
SELECT*fROM AP_TERMS WHERE NAME='20 Day HISH'
--------------------
BANK ACCOUNTS
SELECT*FROM HZ_PARTIES WHERE PARTY_NAME='ICICIH Bank'
SELECT*FROM HZ_PARTY_SITES WHERE PARTY_ID=415791
SELECT*fROM HZ_PARTIES WHERE PARTY_NAME='ICICIH KPHB BRANCH'
SELECT*FROM HZ_PARTY_SITES WHERE PARTY_ID=415805
SELECT*FROM CE_BANK_ACCOUNTS WHERE BANK_ID=415791
-----------------------------
SUPPLIER
SELECT*fROM AP_SUPPLIERS WHERE VENDOR_NAME='DELL SUPPLIER'
SELECT*fROM AP_SUPPLIER_SITES_ALL WHERE VENDOR_ID=39421
SELECT*fROM IBY_EXT_BANK_ACCOUNTS WHERE BANK_ID=415791
----------------------------
AP INVOICE AND PAYMENTS
SELECT*FROM AP_INVOICES_ALL WHERE INVOICE_NUM='DELL1'
SELECT*FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID=212195
SELECT*FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=212195
SELECT*fROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOICE_ID=212195
SELECT*fROM AP_HOLDS_ALL WHERE INVOICE_ID=212195
SELECT*fROM AP_CHECKS_ALL WHERE CHECK_NUMBER='6593' AND BANK_ACCOUNT_NAME='BofA-204'
SELECT*fROM AP_INVOICE_PAYMENTS_ALL WHERE CHECK_ID=77949
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY APPS.HISR_AP_INVOICE_PKG IS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER)
IS
ln_org_id NUMBER;
lb_error_flag BOOLEAN;
lv_message VARCHAR2(4000);
ln_vendor_id NUMBER;
ln_vendor_site_id NUMBER;
ln_term_id NUMBER;
ln_invoice_id NUMBER;
n_cnt NUMBER := 0;
BEGIN
FOR rec_inv_m IN ( SELECT vendor_site_code,
vendor_num,
invoice_ammount,
payment_currency_code,
invoice_currency_code,
terms_name,
operating_unit,
invoice_num,
invoice_date,
invoice_type_lookup_code
FROM AP_JB_INVOICE
WHERE process_flag = 'N'
GROUP BY vendor_site_code,
vendor_num,
invoice_ammount,
payment_currency_code,
invoice_currency_code,
terms_name,
operating_unit,
invoice_num,
invoice_date,
invoice_type_lookup_code)
LOOP
ln_org_id := 0;
lb_error_flag := FALSE;
lv_message := '';
ln_vendor_id := NULL;
ln_vendor_site_id := NULL;
ln_term_id := NULL;
ln_invoice_id := NULL;
BEGIN
SELECT organization_id
INTO ln_org_id
FROM HR_OPERATING_UNITS
WHERE name = rec_inv_m.operating_unit;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lb_error_flag := TRUE;
lv_message := 'invalid operating unit name: '||rec_inv_m.operating_unit;
WHEN OTHERS THEN
lb_error_flag := TRUE;
lv_message := 'Error while validating operating unit: '||SQLERRM;
END;
BEGIN
SELECT vendor_id
INTO ln_vendor_id
FROM AP_SUPPLIERS
WHERE segment1 = rec_inv_m.vendor_num
AND enabled_flag = 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lb_error_flag := TRUE;
lv_message := lv_message || ', '|| 'invalid Supplier number: '||rec_inv_m.vendor_num;
WHEN OTHERS THEN
lb_error_flag := TRUE;
lv_message := lv_message || ', '||'Error while validating Supplier Num: '||SQLERRM;
END;
BEGIN
SELECT vendor_site_id
INTO ln_vendor_site_id
FROM AP_SUPPLIER_SITES_ALL
WHERE vendor_site_code = rec_inv_m.vendor_site_code
AND vendor_id = ln_vendor_id
AND org_id = ln_org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lb_error_flag := TRUE;
lv_message := lv_message || ', '||'invalid Supplier Site code: '||rec_inv_m.vendor_site_code;
WHEN OTHERS THEN
lb_error_flag := TRUE;
lv_message := lv_message || ', '||'Error while validating Supplier Site: '||SQLERRM;
END;
BEGIN
SELECT term_id
INTO ln_term_id
FROM AP_TERMS
WHERE name = rec_inv_m.terms_name;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lb_error_flag := TRUE;
lv_message := lv_message || ', '||'invalid Terms Name: '||rec_inv_m.terms_name;
WHEN OTHERS THEN
lb_error_flag := TRUE;
lv_message := lv_message || ', '||'Error while validating Terms name: '||SQLERRM;
END;
BEGIN
SELECT invoice_id
INTO ln_invoice_id
FROM AP_INVOICES_ALL
WHERE invoice_num = rec_inv_m.invoice_num
AND vendor_id = ln_vendor_id;
lb_error_flag := TRUE;
lv_message := lv_message || ', '||'Invoice alredy exists for same supplier: '||rec_inv_m.invoice_num;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
lb_error_flag := TRUE;
lv_message := lv_message || ', '||'Error while validating Invoice Num: '||SQLERRM;
END;
IF lb_error_flag = FALSE THEN
n_cnt := 1;
INSERT INTO AP_INVOICES_INTERFACE
(invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
vendor_num,
vendor_site_code,
invoice_amount,
invoice_currency_code,
terms_name,
last_update_date,
last_updated_by,
creation_date,
created_by,
source,
payment_currency_code,
operating_unit)
VALUES(AP_INVOICES_INTERFACE_S.nextval,
rec_inv_m.invoice_num,
rec_inv_m.invoice_type_lookup_code,
rec_inv_m.invoice_date,
rec_inv_m.vendor_num,
rec_inv_m.vendor_site_code,
rec_inv_m.invoice_ammount,
rec_inv_m.invoice_currency_code,
rec_inv_m.terms_name,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
'INVOICE GATEWAY',
rec_inv_m.payment_currency_code,
rec_inv_m.operating_unit);
FOR rec_inv_c IN (SELECT *
FROM AP_JB_INVOICE
WHERE process_flag = 'N'
AND invoice_num = rec_inv_m.invoice_num
and vendor_num = rec_inv_m.vendor_num
and invoice_date = rec_inv_m.invoice_date
and vendor_site_code = rec_inv_m.vendor_site_code)
LOOP
INSERT INTO AP_INVOICE_LINES_INTERFACE
(invoice_id,
invoice_line_id,
line_type_lookup_code,
amount,
dist_code_combination_id,
last_updated_by,
last_update_date,
created_by,
creation_date)
VALUES(AP_INVOICES_INTERFACE_S.currval,
AP_INVOICE_LINES_INTERFACE_S.NEXTVAL,
rec_inv_c.line_type_lookup_code,
rec_inv_c.ammount,
17347,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE);
END LOOP;
UPDATE AP_JB_INVOICE
SET process_flag = 'S'
WHERE invoice_num = rec_inv_m.invoice_num
AND vendor_num = rec_inv_m.vendor_num
AND invoice_date = rec_inv_m.invoice_date
AND vendor_site_code = rec_inv_m.vendor_site_code;
ELSE
UPDATE AP_JB_INVOICE
SET process_flag = 'E',
error_message = lv_message
WHERE invoice_num = rec_inv_m.invoice_num
AND vendor_num = rec_inv_m.vendor_num
AND invoice_date = rec_inv_m.invoice_date
AND vendor_site_code = rec_inv_m.vendor_site_code;
END IF;
END LOOP;
IF n_cnt = 0 THEN
RETCODE := 1;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
ROLLBACK;
RETCODE := 2;
END;
END;
/
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE APPS.RSQUARE_SUPPLIER_SITE_PKG
IS
PROCEDURE MSG(P_MESSAGE IN VARCHAR2);
PROCEDURE MAIN(
ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER
);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.RSQUARE_SUPPLIER_SITE_PKG
IS
PROCEDURE MSG(P_MESSAGE IN VARCHAR2)
IS
BEGIN
FND_FILE.PUT_LINE(FND_FILE.LOG,P_MESSAGE);
END;
PROCEDURE MAIN(
ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER
)
IS
lb_error_flag BOOLEAN;
lv_error_message VARCHAR2(4000);
lv_interface_id NUMBER;
lv_invoice_currency_code FND_CURRENCIES.currency_code%TYPE;
lv_payment_currency_code FND_CURRENCIES.currency_code%TYPE;
lv_terms_name AP_TERMS.name%TYPE;
ln_org_id HR_OPERATING_UNITS.organization_id%TYPE; -- To store the org Id
ln_vendor_id AP_SUPPLIERS.vendor_id%TYPE;
lv_vendor_site_code AP_SUPPLIER_SITES_ALL.vendor_site_code%TYPE;
ln_cnt NUMBER :=0;
ln_s_cnt NUMBER :=0;
ln_e_cnt NUMBER :=0;
BEGIN
--To Validate Interface id
SELECT AP_SUPPLIER_SITES_INT_S.NEXTVAL
INTO lv_interface_id
FROM DUAL;
FOR REC_SUP_DATA IN(SELECT A.*,
A.ROWID ROW_ID
FROM RSQUARE_PO_VENDOR_STG8 A
WHERE NVL(record_status,'N')='N' )
LOOP
lb_error_flag :=FALSE;
lv_error_message :='';
lv_invoice_currency_code :=NULL;
lv_payment_currency_code :=NULL;
ln_cnt :=ln_cnt+1;
ln_org_id :=NULL;
lv_vendor_site_code :=NULL;
lv_terms_name :=NULL;
--To Validate Vendor Name
BEGIN
SELECT vendor_id
INTO ln_vendor_id
FROM AP_SUPPLIERS
WHERE UPPER(vendor_name)=UPPER(REC_SUP_DATA.VENDOR_NAME)
AND enabled_flag ='Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_error_message :='Invlaid Vendor Name :-'||REC_SUP_DATA.VENDOR_NAME;
lb_error_flag :=TRUE;
WHEN OTHERS THEN
lb_error_flag :=TRUE;
lv_error_message :='Error While Validating Vendor Name:-'||SQLERRM;
END;
--To Validate Invoice Currency Code
BEGIN
SELECT currency_code
INTO lv_invoice_currency_code
FROM FND_CURRENCIES
WHERE UPPER(currency_code)=UPPER(REC_SUP_DATA.INVOICE_CURRENCY_CODE)
AND enabled_flag ='Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lb_error_flag :=TRUE;
lv_error_message :=lv_error_message||','||'Invalid Invoice Currency Code :-'||REC_SUP_DATA.INVOICE_CURRENCY_CODE;
WHEN OTHERS THEN
lb_error_flag :=TRUE;
lv_error_message :=lv_error_message||','||'Error While Validating Invoice Currency Code :-'||SQLERRM;
END;
--To Validate Payment Currency Code
BEGIN
SELECT currency_code
INTO lv_payment_currency_code
FROM FND_CURRENCIES
WHERE UPPER(currency_code)=UPPER(REC_SUP_DATA.PAYMENT_CURRENCY_CODE)
AND enabled_flag ='Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
lb_error_flag :=TRUE;
lv_error_message :=lv_error_message||','||'Invalid Payment Currency Code :-'||REC_SUP_DATA.PAYMENT_CURRENCY_CODE;
WHEN OTHERS THEN
lb_error_flag :=TRUE;
lv_error_message :=lv_error_message||','||'Error While Validating Payment Currency Code :-'||SQLERRM;
END;
--To Validate Operating Unit Name
BEGIN
SELECT organization_id
INTO ln_org_id
FROM HR_OPERATING_UNITS
WHERE UPPER(NAME)=UPPER(REC_SUP_DATA.OPERATING_UNIT_NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
lb_error_flag :=TRUE;
lv_error_message :=lv_error_message||','||'Operating_Unit_Name Does not Exists :-'||REC_SUP_DATA.OPERATING_UNIT_NAME;
WHEN OTHERS THEN
lb_error_flag :=TRUE;
lv_error_message :=lv_error_message||','||'Error While Validating Operating_Unit_Name:-'||SQLERRM;
END;
--To Validate Vendor Site Code
BEGIN
SELECT vendor_site_code
INTO lv_vendor_site_code
FROM ap_supplier_sites_all
WHERE UPPER(vendor_site_code)=UPPER(REC_SUP_DATA.VENDOR_SITE_CODE)
AND org_id=ln_org_id
AND vendor_id=ln_vendor_id;
lb_error_flag :=TRUE;
lv_error_message :=lv_error_message||','||'Vendor_Site_Code Already Exists :-'||REC_SUP_DATA.VENDOR_SITE_CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
lb_error_flag :=TRUE;
lv_error_message :=lv_error_message||','||'Error While Validating Vendor_Site_Code:-'||SQLERRM;
END;
--To Validate Terms Name
BEGIN
SELECT name
INTO lv_terms_name
FROM AP_TERMS
WHERE UPPER(name)=UPPER(REC_SUP_DATA.TERMS_NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
lb_error_flag :=TRUE;
lv_error_message :=lv_error_message||','||'Invalid Terms Name :-'||REC_SUP_DATA.TERMS_NAME;
WHEN OTHERS THEN
lb_error_flag :=TRUE;
lv_error_message :=lv_error_message||','||'Error While Validating Terms Name:-'||SQLERRM;
END;
IF lb_error_flag=FALSE THEN
ln_s_cnt :=ln_s_cnt+1;
UPDATE RSQUARE_PO_VENDOR_STG8
SET record_status='S'
WHERE rowid=REC_SUP_DATA.ROW_ID;
INSERT INTO AP_SUPPLIER_SITES_INT
( VENDOR_ID,
VENDOR_SITE_INTERFACE_ID,
VENDOR_SITE_CODE,
ADDRESS_LINE1,
CITY,
ZIP,
COUNTRY,
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY_CODE,
MATCH_OPTION,
EMAIL_ADDRESS,
TERMS_NAME,
OPERATING_UNIT_NAME,
LANGUAGE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
VALUES( ln_vendor_id ,
lv_interface_id,
REC_SUP_DATA.VENDOR_SITE_CODE,
REC_SUP_DATA.ADDRESS_LINE1,
REC_SUP_DATA.CITY,
REC_SUP_DATA.ZIP,
REC_SUP_DATA.COUNTRY,
lv_invoice_currency_code,
lv_payment_currency_code,
REC_SUP_DATA.MATCH_OPTION,
REC_SUP_DATA.EMAIL_ADDRESS,
lv_terms_name,
REC_SUP_DATA.OPERATING_UNIT_NAME,
REC_SUP_DATA.LANGUAGE,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID
);
ELSE
ln_e_cnt :=ln_e_cnt+1;
UPDATE RSQUARE_PO_VENDOR_STG8
SET record_status='E',
error_message=lv_error_message
WHERE rowid=REC_SUP_DATA.ROW_ID;
END IF;
END LOOP;
MSG('Total Records got Processed :-'||ln_cnt);
MSG('Total Inserted into Open Interface :-'||ln_s_cnt);
MSG('Total got Rejected :-'||ln_e_cnt);
IF ln_s_cnt=0 THEN
RETCODE :=1;
END IF;
EXCEPTION
WHEN OTHERS THEN
MSG('P_ERROR_MESSAGE :-'||SQLERRM);
ROLLBACK;
RETCODE :=2;
END;
END;
/
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin
fnd_client_info.set_org_context(204);
end;
SELECT FSP.VAT_REGISTRATION_NUM,
FND_PROFILE.VALUE('RU_TAX_KPP') VAT_CODE,
aia.invoice_num,
aia.invoice_date date_of_invoice,
pv.vendor_name name_of_counteragent,
pv.vat_registration_num fiscal_code_of_counteragent,
aia.description,
aia.invoice_amount,
(SELECT AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_V aid,
AP_TAX_CODES ATC
WHERE VAT_CODE = 'VAT-15%'
AND AIA.INVOICE_ID = AID.INVOICE_ID
AND AID.TAX_CODE_ID = ATC.TAX_ID
AND AID.LINE_TYPE_LOOKUP_CODE = 'ITEM' ) VAT_15,
(SELECT AMOUNT * TAX_RATE /100
FROM AP_INVOICE_DISTRIBUTIONS_V aid,
AP_TAX_CODES ATC
WHERE VAT_CODE = 'VAT-15%'
AND AIA.INVOICE_ID = AID.INVOICE_ID
AND AID.TAX_CODE_ID = ATC.TAX_ID
AND AID.LINE_TYPE_LOOKUP_CODE = 'ITEM' ) VAT_15,
(SELECT AMOUNT
FROM AP_INVOICE_DISTRIBUTIONS_V aid,
AP_TAX_CODES ATC
WHERE VAT_CODE = 'VAT- 5%'
AND AIA.INVOICE_ID = AID.INVOICE_ID
AND AID.TAX_CODE_ID = ATC.TAX_ID
AND AID.LINE_TYPE_LOOKUP_CODE = 'ITEM' ) VAT_5 ,
(SELECT sum(AMOUNT * TAX_RATE )/100
FROM AP_INVOICE_DISTRIBUTIONS_V aid,
AP_TAX_CODES ATC
WHERE VAT_CODE = 'VAT- 5%'
AND AIA.INVOICE_ID = AID.INVOICE_ID
AND AID.TAX_CODE_ID = ATC.TAX_ID
AND AID.LINE_TYPE_LOOKUP_CODE = 'ITEM' ) VAT_5
FROM FINANCIALS_SYSTEM_PARAMETERS FSP,
AP_INVOICES_V AIA,
PO_VENDORS PV
WHERE fsp.org_id = 204
AND aia.org_id = fsp.org_id
AND aia.vendor_id = pv.vendor_id
AND aia.INVOICE_NUM = 'RS10000'
SELECT * FROM AP_TAX_CODES
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_V WHERE INVOICE_ID = 63247
AND LINE_TYPE_LOOKUP_CODE = 'ITEM'
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOAD DATA
INFILE"/r12/applmgr/VIS/apps/apps_st/appl/ap/12.0.0/data/HISH_NL_VODAFONE_INVOICE.txt"
INSERT INTO TABLE HISH_AP_STAGE
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
INVOICE_DATE ,
INVOICE_TYPE_LOOKUP_CODE,
INVOICE_NUM,
OPERATING_UNIT,
TERMS_NAME ,
INVOICE_CURRENCY_CODE,
PAYMENT_CURRENCY,
INVOICE_AMOUNT,
VENDOR_NUM ,
VENDOR_SITE_CODE,
AMOUNT,
LINE_TYPE_LOOKUP_CODE ,
PROCESS_FLAG CONSTANT "N",
CREATION_DATE "SYSDATE",
CREATED_BY CONSTANT "-1",
LAST_UPDATE_DATE "SYSDATE",
LAST_UPDATED_BY CONSTANT "-1")
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment