[Home] [Help]
1589: CURSOR batch_name_cur(p_begin_date VARCHAR2,
1590: p_end_date VARCHAR2,
1591: l_business_group_id NUMBER,
1592: l_set_of_books_id NUMBER) IS
1593: SELECT /*+ INDEX(pdi2 PSP_DISTRIBUTION_INTERFACE_N2)*/
1594: batch_name
1595: , count (*)
1596: , min (distribution_date)
1597: , max (distribution_date)
1594: batch_name
1595: , count (*)
1596: , min (distribution_date)
1597: , max (distribution_date)
1598: FROM psp_distribution_interface pdi
1599: WHERE pdi.distribution_date
1600: BETWEEN nvl (fnd_date.canonical_to_date (p_begin_date), hr_general.start_of_time)
1601: AND nvl (fnd_date.canonical_to_date (p_end_date), hr_general.end_of_time)
1602: AND business_group_id = l_business_group_id
1602: AND business_group_id = l_business_group_id
1603: AND set_of_books_id = l_set_of_books_id
1604: AND NOT EXISTS ---- See is there is atleast one record that is falling out of the period specified OR not yet transferred for the batch selected above
1605: (
1606: SELECT /*+ INDEX(pdi2 PSP_DISTRIBUTION_INTERFACE_N1)*/
1607: 1
1608: FROM psp_distribution_interface pdi2
1609: WHERE pdi2.batch_name = pdi.batch_name
1610: AND (
1604: AND NOT EXISTS ---- See is there is atleast one record that is falling out of the period specified OR not yet transferred for the batch selected above
1605: (
1606: SELECT /*+ INDEX(pdi2 PSP_DISTRIBUTION_INTERFACE_N1)*/
1607: 1
1608: FROM psp_distribution_interface pdi2
1609: WHERE pdi2.batch_name = pdi.batch_name
1610: AND (
1611: pdi2.distribution_date
1612: NOT BETWEEN nvl (fnd_date.canonical_to_date (p_begin_date), hr_general.start_of_time)
1621:
1622: CURSOR batch_cur(p_batch_name VARCHAR2,
1623: l_business_group_id NUMBER,
1624: l_set_of_books_id NUMBER) IS
1625: SELECT /*+ INDEX(pdi2 PSP_DISTRIBUTION_INTERFACE_N2)*/
1626: rowid
1627: , distribution_interface_id
1628: , person_id
1629: , assignment_id
1671: , suspense_org_account_id
1672: , currency_code
1673: , suspense_auto_glccid
1674: , suspense_auto_exp_type
1675: FROM psp_distribution_interface pdi
1676: WHERE pdi.batch_name LIKE p_batch_name
1677: AND pdi.distribution_date
1678: BETWEEN nvl (fnd_date.canonical_to_date (p_begin_date), hr_general.start_of_time)
1679: AND nvl (fnd_date.canonical_to_date (p_end_date), hr_general.end_of_time)
1680: AND business_group_id = l_business_group_id
1681: AND set_of_books_id = l_set_of_books_id
1682: AND NOT EXISTS
1683: (
1684: SELECT /*+ INDEX(pdi2 PSP_DISTRIBUTION_INTERFACE_N1)*/
1685: 1
1686: FROM psp_distribution_interface pdi2
1687: WHERE pdi2.batch_name = pdi.batch_name
1688: AND (
1682: AND NOT EXISTS
1683: (
1684: SELECT /*+ INDEX(pdi2 PSP_DISTRIBUTION_INTERFACE_N1)*/
1685: 1
1686: FROM psp_distribution_interface pdi2
1687: WHERE pdi2.batch_name = pdi.batch_name
1688: AND (
1689: pdi2.distribution_date
1690: NOT BETWEEN nvl (fnd_date.canonical_to_date (p_begin_date), hr_general.start_of_time)
2066:
2067: -- Deletion of archived data from original table
2068:
2069: FORALL k IN 1 .. r_batch_rec.t_distribution_interface_id.COUNT
2070: DELETE FROM psp_distribution_interface
2071: WHERE rowid = r_batch_rec.t_row_id (k);
2072:
2073: -- Updating the count of archived data for the batch in the control table
2074:
2455:
2456: -- Transferring data to main table
2457: FORALL k IN 1 .. r_batch_rec.t_distribution_interface_id.COUNT
2458: INSERT
2459: INTO psp_distribution_interface (distribution_interface_id
2460: , person_id
2461: , assignment_id
2462: , element_type_id
2463: , distribution_date