[Home] [Help]
26: g_pk1_name2 CONSTANT VARCHAR2(30) := 'FROM_ORGANIZATION_ID';
27: g_pk2_name2 CONSTANT VARCHAR2(30) := 'TO_ORGANIZATION_ID';
28: g_pub_item2 CONSTANT VARCHAR2(30) := 'CSM_MTL_INTERORG_PARAMS';
29:
30: g_acc_table_name4 CONSTANT VARCHAR2(30) := 'CSM_INV_ORG_FREIGHTS_ACC';
31: g_publication_item_name4 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
32: CSM_ACC_PKG.t_publication_item_list('CSM_INV_ORG_FREIGHTS');
33: g_pk1_name4 CONSTANT VARCHAR2(30) := 'ORGANIZATION_ID';
34: g_pk2_name4 CONSTANT VARCHAR2(30) := 'FREIGHT_CODE';
435: AND sysdate between sm.start_date_active(+) AND nvl(sm.end_date_active(+), sysdate+1)
436: AND EXISTS(SELECT 1 FROM CSP_INV_LOC_ASSIGNMENTS ass
437: WHERE ass.organization_id = oft.organization_id
438: AND ass.RESOURCE_ID=au.resource_id)
439: AND NOT EXISTS(SELECT 1 FROM CSM_INV_ORG_FREIGHTS_ACC acc
440: WHERE acc.organization_id = oft.organization_id
441: AND acc.freight_code=oft.freight_code
442: AND NVL(acc.ship_method_code,'X') = NVL(sm.ship_method_code,'X')
443: AND acc.user_id=au.user_id);
445: -- updates cur
446: CURSOR c_inv_org_freights_upd_csr(b_lrd DATE)
447: IS
448: SELECT acc.access_id,acc.user_id
449: FROM CSM_INV_ORG_FREIGHTS_ACC acc,ORG_FREIGHT_TL oft,asg_user au
450: WHERE acc.organization_id = oft.organization_id
451: AND acc.freight_code=oft.freight_code
452: AND au.user_id=acc.user_id
453: AND oft.language=au.language
453: AND oft.language=au.language
454: AND oft.last_update_date > b_lrd
455: UNION
456: SELECT acc.access_id,acc.user_id
457: FROM CSM_INV_ORG_FREIGHTS_ACC acc
458: WHERE acc.SHIP_METHOD_CODE IN (
459: SELECT FL.LOOKUP_CODE FROM FND_LOOKUP_VALUES_VL fl
460: WHERE FL.LOOKUP_TYPE = 'SHIP_METHOD'
461: AND FL.VIEW_APPLICATION_ID = 3
464: -- deletes cur
465: CURSOR c_inv_org_freights_del_csr
466: IS
467: SELECT acc.access_id,acc.user_id
468: FROM CSM_INV_ORG_FREIGHTS_ACC acc
469: WHERE NOT EXISTS (SELECT 1 FROM ORG_FREIGHT_TL oft,CSP_INV_LOC_ASSIGNMENTS ass,asg_user au
470: WHERE acc.organization_id = oft.organization_id
471: AND acc.freight_code=oft.freight_code
472: AND acc.user_id=au.user_id
474: AND ass.organization_id = oft.organization_id
475: AND ass.RESOURCE_ID=au.resource_id)
476: UNION
477: (SELECT acc.access_id,acc.user_id
478: FROM CSM_INV_ORG_FREIGHTS_ACC acc
479: WHERE acc.ship_method_code IS NOT NULL
480: AND NOT EXISTS (SELECT 1 FROM WSH_CARRIER_SHIP_METHODS_V sm
481: WHERE acc.organization_id = sm.organization_id
482: AND acc.ship_method_code=sm.ship_method_code
484: AND SM.ENABLED_FLAG='Y'
485: AND sysdate between sm.start_date_active AND nvl(sm.end_date_active, sysdate+1))
486: UNION ALL
487: SELECT acc.access_id,acc.user_id
488: FROM CSM_INV_ORG_FREIGHTS_ACC acc
489: WHERE acc.ship_method_code IS NULL
490: AND EXISTS (SELECT 1 FROM WSH_CARRIER_SHIP_METHODS_V sm
491: WHERE acc.organization_id = sm.organization_id
492: AND acc.freight_code=sm.freight_code
523: , P_TIMESTAMP => sysdate
524: );
525:
526: FORALL i IN 1..l_tab_access_id.COUNT
527: DELETE FROM CSM_INV_ORG_FREIGHTS_ACC WHERE access_id = l_tab_access_id(i);
528:
529: COMMIT;
530: END LOOP;
531: CLOSE c_inv_org_freights_del_csr;
573: CSM_UTIL_PKG.LOG('Bulk inserted ' || l_tab_access_id.count || ' records into CSM_INTERORG_FREIGHTS_ACC' ,
574: 'CSM_MTL_PARAMETERS_EVENT_PKG.REFRESH_FREIGHTS_ACC',FND_LOG.LEVEL_EVENT);
575:
576: FORALL i IN 1..l_tab_access_id.COUNT
577: INSERT INTO CSM_INV_ORG_FREIGHTS_ACC (access_id, user_id,organization_id, freight_code,ship_method_code,
578: created_by, creation_date, last_updated_by, last_update_date )
579: VALUES (l_tab_access_id(i), l_tab_user_id(i),l_tab_org(i),l_tab_freight(i),l_tab_shipM(i),
580: fnd_global.user_id, sysdate, fnd_global.user_id, sysdate);
581: