[Home] [Help]
644: po_asl_api_pvt.log('number of rows inserted into po_supplier_item_capacity:'
645: || SQL%ROWCOUNT);
646: l_progress := 30;
647:
648: INSERT INTO po_supplier_item_tolerance
649: ( asl_id ,
650: using_organization_id ,
651: number_of_days ,
652: tolerance ,
705: request_id ,
706: program_application_id ,
707: program_id ,
708: program_update_date
709: FROM po_supplier_item_tolerance_gt
710: WHERE user_key IN
711: (SELECT user_key
712: FROM po_approved_supplier_list_gt
713: WHERE process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
712: FROM po_approved_supplier_list_gt
713: WHERE process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
714: AND process_action = PO_ASL_API_PUB.g_ACTION_ADD
715: );
716: po_asl_api_pvt.log('Num of rows inserted into po_supplier_item_tolerance:'
717: || SQL%ROWCOUNT);
718: l_progress := 35;
719: ------------------------------CREATE MODE END---------------------------------
720:
1163: po_asl_api_pvt.log('number of rows updated in po_supplier_item_capacity:'
1164: || SQL%ROWCOUNT);
1165: l_progress := 60;
1166:
1167: UPDATE po_supplier_item_tolerance PSIT
1168: SET
1169: (PSIT.last_update_date ,
1170: PSIT.last_updated_by ,
1171: PSIT.attribute_category ,
1211: GT.request_id ,
1212: GT.program_application_id,
1213: GT.program_id ,
1214: GT.program_update_date
1215: FROM po_supplier_item_tolerance_gt GT,
1216: po_approved_supplier_list_gt ASLGT
1217: WHERE GT.user_key = ASLGT.user_key
1218: AND GT.asl_id = PSIT.asl_id
1219: AND GT.using_organization_id = PSIT.using_organization_id
1227: (SELECT GT.asl_id ,
1228: GT.using_organization_id ,
1229: GT.tolerance_dsp ,
1230: GT.number_of_days_dsp
1231: FROM po_supplier_item_tolerance_gt GT,
1232: po_approved_supplier_list_gt ASLGT
1233: WHERE GT.user_key = ASLGT.user_key
1234: AND GT.asl_id = PSIT.asl_id
1235: AND GT.using_organization_id = PSIT.using_organization_id
1238: AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1239: AND GT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1240: AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1241:
1242: po_asl_api_pvt.log('number of rows updated in po_supplier_item_tolerance:'
1243: || SQL%ROWCOUNT);
1244: l_progress := 65;
1245: ------------------------------UPDATE MODE ENDS--------------------------------
1246:
1414: po_asl_api_pvt.log('3.number of rows deleted from po_supplier_item_capacity:'
1415: || SQL%ROWCOUNT);
1416: l_progress := 90;
1417:
1418: DELETE FROM po_supplier_item_tolerance PSIT
1419: WHERE (asl_id, using_organization_id, number_of_days, tolerance) IN
1420: (SELECT GT.asl_id ,
1421: GT.using_organization_id ,
1422: GT.number_of_days_dsp ,
1420: (SELECT GT.asl_id ,
1421: GT.using_organization_id ,
1422: GT.number_of_days_dsp ,
1423: GT.tolerance_dsp
1424: FROM po_supplier_item_tolerance_gt GT,
1425: po_approved_supplier_list_gt ASLGT
1426: WHERE ASLGT.user_key = GT.user_key
1427: AND GT.process_action = PO_ASL_API_PUB.g_ACTION_DELETE
1428: AND GT.asl_id = PSIT.asl_id
1430: AND GT.number_of_days_dsp = PSIT.number_of_days
1431: AND GT.tolerance_dsp = PSIT.tolerance
1432: AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1433:
1434: po_asl_api_pvt.log('1.number of rows deleted from po_supplier_item_tolerance:'
1435: || SQL%ROWCOUNT);
1436: l_progress := 95;
1437:
1438: --Delete tolerance, if asl_attributes modified with enable_vmi_flag unchekced
1435: || SQL%ROWCOUNT);
1436: l_progress := 95;
1437:
1438: --Delete tolerance, if asl_attributes modified with enable_vmi_flag unchekced
1439: DELETE FROM po_supplier_item_tolerance PSIT
1440: WHERE (asl_id, using_organization_id) IN
1441: (SELECT PAA.asl_id,
1442: PAA.using_organization_id
1443: FROM po_asl_attributes PAA
1452: WHERE ASLGT.asl_id = PSIT.asl_id
1453: AND ASLGT.using_organization_id = PSIT.using_organization_id);
1454:
1455: l_progress := 97;
1456: po_asl_api_pvt.log('2.number of rows deleted from po_supplier_item_tolerance:'
1457: || SQL%ROWCOUNT);
1458:
1459: --Delete tolerance, if asl_attributes doesn't exist
1460: DELETE FROM po_supplier_item_tolerance PSIT
1456: po_asl_api_pvt.log('2.number of rows deleted from po_supplier_item_tolerance:'
1457: || SQL%ROWCOUNT);
1458:
1459: --Delete tolerance, if asl_attributes doesn't exist
1460: DELETE FROM po_supplier_item_tolerance PSIT
1461: WHERE NOT EXISTS
1462: (SELECT 1
1463: FROM po_asl_attributes PAA
1464: WHERE PAA.asl_id = PSIT.asl_id
1469: FROM po_approved_supplier_list_gt ASLGT
1470: WHERE ASLGT.asl_id = PSIT.asl_id
1471: AND ASLGT.using_organization_id = PSIT.using_organization_id);
1472:
1473: po_asl_api_pvt.log('3.number of rows deleted from po_supplier_item_tolerance:'
1474: || SQL%ROWCOUNT);
1475: l_progress := 98;
1476: ------------------------------DELETE MODE END---------------------------------
1477:
1964:
1965: --Name: log
1966:
1967: --Function:
1968: -- For logging po_supplier_item_tolerance_rec records
1969:
1970: --Parameters:
1971:
1972: --IN:
1969:
1970: --Parameters:
1971:
1972: --IN:
1973: --p_tol_rec po_supplier_item_tolerance_rec
1974:
1975: --End of Comments
1976: --------------------------------------------------------------------------------
1977:
1975: --End of Comments
1976: --------------------------------------------------------------------------------
1977:
1978: PROCEDURE log(
1979: p_tol_rec IN po_supplier_item_tolerance_rec
1980: )
1981: AS
1982:
1983: BEGIN