[Home] [Help]
278: -- AP tax payer id available check
279: -- Bug 14563117 Start
280: DECLARE
281: Cursor C_TaxPayer Is
282: -- SELECT DISTINCT 'Y' taxpayer FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
283: SELECT /*+ cardinality(PV.PAV,1) */ 'Y' taxpayer FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
284: -- Bug 14563117 End
285: WHERE NVL(pv.employee_id, - 99) = papf.person_id (+)
286: AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
279: -- Bug 14563117 Start
280: DECLARE
281: Cursor C_TaxPayer Is
282: -- SELECT DISTINCT 'Y' taxpayer FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
283: SELECT /*+ cardinality(PV.PAV,1) */ 'Y' taxpayer FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
284: -- Bug 14563117 End
285: WHERE NVL(pv.employee_id, - 99) = papf.person_id (+)
286: AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
287: AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
504: l_SUPP_AWT_CODE_ID_SEQ JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_CODE_ID%TYPE;
505: l_SUPP_AWT_TYPE_ID_SEQ JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_TYPE_ID%TYPE;
506: l_tax_id ap_tax_codes.tax_id%TYPE;
507: l_SUPP_AWT_TYPE_ID_TYPES JL_ZZ_AP_SUPP_AWT_TYPES.SUPP_AWT_TYPE_ID%TYPE;
508: l_VENDOR_ID PO_VENDORS.VENDOR_ID%TYPE;
509: l_INV_DISTRIB_AWT_ID_INV JL_ZZ_AP_INV_DIS_WH_ALL.INV_DISTRIB_AWT_ID%TYPE;
510: l_INVOICE_ID_INA AP_INVOICES_ALL.INVOICE_ID%TYPE;
511: l_DISTRIBUTION_LINE_NUMBER_IND AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER%TYPE;
512: l_INVOICE_DISTRIBUTION_ID_IND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE;
519: CURSOR CUR3(l_TAXPAYERID_C NUMBER) IS
520: SELECT APINA.INVOICE_ID,
521: APIND.DISTRIBUTION_LINE_NUMBER,
522: APIND.INVOICE_DISTRIBUTION_ID
523: FROM PO_VENDORS PV,
524: AP_INVOICES APINA,
525: AP_INVOICE_DISTRIBUTIONS APIND,
526: PER_ALL_PEOPLE_F PAPF
527: WHERE PV.VENDOR_ID = APINA.VENDOR_ID
919: BEGIN
920:
921: SELECT SAWT.SUPP_AWT_TYPE_ID
922: INTO l_SUPP_AWT_TYPE_ID_CD
923: FROM PO_VENDORS PV,
924: JL_ZZ_AP_SUPP_AWT_TYPES SAWT,
925: PER_ALL_PEOPLE_F PAPF
926: WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
927: AND NVL(pv.employee_id, - 99) = papf.person_id (+)
1054: -- TO GET THE CURRENT MONTH SUPP_AWT_CODE_ID FOR THE PRESENT TAX_ID
1055:
1056: SELECT SAWTC.SUPP_AWT_CODE_ID INTO l_SUPP_AWT_CODE_ID_CD
1057: FROM JL_ZZ_AP_SUP_AWT_CD SAWTC,
1058: PO_VENDORS PV, JL_ZZ_AP_SUPP_AWT_TYPES SAWT, PER_ALL_PEOPLE_F PAPF
1059: WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
1060: AND NVL(pv.employee_id, - 99) = papf.person_id (+)
1061: AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
1062: AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
1366: BEGIN
1367:
1368: SELECT JL_ZZ_AP_SUPP_AWT_TYPES_s.nextval INTO l_SUPP_AWT_TYPE_ID_TYPES FROM dual;
1369:
1370: SELECT PV.VENDOR_ID INTO l_VENDOR_ID FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
1371: WHERE NVL(pv.employee_id, - 99) = papf.person_id (+)
1372: AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
1373: AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
1374: --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
1427: --- TO INSERT INTO JL_ZZ_AP_SUP_AWT_CD (3.3.2)
1428:
1429: SELECT SAWT.SUPP_AWT_TYPE_ID
1430: INTO l_SUPP_AWT_TYPE_ID_CD
1431: FROM PO_VENDORS PV,
1432: JL_ZZ_AP_SUPP_AWT_TYPES SAWT,
1433: PER_ALL_PEOPLE_F PAPF
1434: WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
1435: AND NVL(pv.employee_id, - 99) = papf.person_id (+)
1578:
1579: SELECT SAWTC.SUPP_AWT_CODE_ID
1580: INTO l_SUPP_AWT_CODE_ID_CD
1581: FROM JL_ZZ_AP_SUP_AWT_CD SAWTC,
1582: PO_VENDORS PV,
1583: JL_ZZ_AP_SUPP_AWT_TYPES SAWT,
1584: PER_ALL_PEOPLE_F PAPF
1585: WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
1586: AND NVL(pv.employee_id, - 99) = papf.person_id (+)
1861: l_SUPP_AWT_CODE_ID_CD JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_CODE_ID%TYPE;
1862: l_SUPP_AWT_TYPE_ID_CD JL_ZZ_AP_SUP_AWT_CD_ALL.SUPP_AWT_TYPE_ID%TYPE;
1863: l_tax_id ap_tax_codes.tax_id%TYPE;
1864: l_SUPP_AWT_TYPE_ID_TYPES JL_ZZ_AP_SUPP_AWT_TYPES.SUPP_AWT_TYPE_ID%TYPE;
1865: l_VENDOR_ID PO_VENDORS.VENDOR_ID%TYPE;
1866: l_INV_DISTRIB_AWT_ID_INV JL_ZZ_AP_INV_DIS_WH_ALL.INV_DISTRIB_AWT_ID%TYPE;
1867: l_INVOICE_ID_INA AP_INVOICES_ALL.INVOICE_ID%TYPE;
1868: l_DISTRIBUTION_LINE_NUMBER_IND AP_INVOICE_DISTRIBUTIONS_ALL.DISTRIBUTION_LINE_NUMBER%TYPE;
1869: l_INVOICE_DISTRIBUTION_ID_IND AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID%TYPE;
1884: CURSOR CUR4(l_TAXPAYERID_C NUMBER) IS
1885: SELECT APINA.INVOICE_ID,
1886: APIND.DISTRIBUTION_LINE_NUMBER,
1887: APIND.INVOICE_DISTRIBUTION_ID
1888: FROM PO_VENDORS PV,
1889: AP_INVOICES APINA,
1890: AP_INVOICE_DISTRIBUTIONS APIND,
1891: PER_ALL_PEOPLE_F PAPF
1892: WHERE PV.VENDOR_ID = APINA.VENDOR_ID
1996: END IF;
1997: -- AP tax payer id available check
1998:
1999: BEGIN
2000: SELECT DISTINCT 'Y' INTO l_taxpayer FROM PO_VENDORS PV, PER_ALL_PEOPLE_F PAPF
2001: WHERE NVL(pv.employee_id, - 99) = papf.person_id (+)
2002: AND NVL(papf.EFFECTIVE_START_DATE, SYSDATE) <= SYSDATE
2003: AND NVL(papf.EFFECTIVE_END_DATE, SYSDATE) >= SYSDATE
2004: --bug 8530918 AND NVL(papf.national_identifier, NVL(pv.individual_1099, pv.num_1099)) = TO_CHAR(l_TAXPAYER_ID);
2108: SAWTC.SUPP_AWT_CODE_ID
2109: INTO l_SUPP_AWT_TYPE_ID_CD,
2110: l_SUPP_AWT_CODE_ID_CD
2111: FROM JL_ZZ_AP_SUP_AWT_CD SAWTC,
2112: PO_VENDORS PV,
2113: JL_ZZ_AP_SUPP_AWT_TYPES SAWT,
2114: PER_ALL_PEOPLE_F PAPF
2115: WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
2116: AND NVL(pv.employee_id, - 99) = papf.person_id (+)
2232:
2233: SELECT SAWTC.SUPP_AWT_CODE_ID
2234: INTO l_SUPP_AWT_CODE_ID_CD
2235: FROM JL_ZZ_AP_SUP_AWT_CD SAWTC,
2236: PO_VENDORS PV,
2237: JL_ZZ_AP_SUPP_AWT_TYPES SAWT,
2238: PER_ALL_PEOPLE_F PAPF
2239: WHERE SAWT.VENDOR_ID = PV.VENDOR_ID
2240: AND NVL(pv.employee_id, - 99) = papf.person_id (+)