Order Booked Workflow not populated

Scenario - Order is in Booked status but order lines are not populated in WSH (shipping) tables. 

SET serveroutput ON
SET verify OFF
SPOOL om_14578684.lst;
DECLARE
  l_result    VARCHAR2(30);
  l_db_name   VARCHAR2(60);
  l_line_id   NUMBER;
  l_file_name VARCHAR2(255);
  CURSOR lines
  IS
    SELECT ool.line_id, ool.flow_status_code
    FROM oe_order_lines_all ool
    WHERE ool.header_id             IN (435881) -- header_id
    AND ool.open_flag               = 'Y'
    AND ool.booked_flag             = 'Y'
    AND NVL(ool.fulfilled_flag,'N') = 'N'
    AND ool.flow_status_code        = 'BOOKED'
    AND Nvl(ool.shipping_interfaced_flag,'N')    = 'N'
    AND EXISTS (SELECT 1
    FROM wf_items wfs
    WHERE wfs.item_key        = TO_CHAR(ool.line_id)
    AND wfs.item_type         = 'OEOL'
    AND wfs.end_date       IS NULL
    ) ;

BEGIN
  Oe_debug_pub.debug_ON;
  Oe_debug_pub.initialize;
  Oe_debug_pub.setdebuglevel(5);
  l_file_name := Oe_debug_pub.set_debug_mode('FILE');
  Dbms_Output.put_line('DEBUG FILE IS located AT :' || l_file_name);
  oe_debug_pub.ADD('DEBUG FILE IS located AT :' || l_file_name);

  select name INTO l_db_name from V$DATABASE;
  oe_debug_pub.ADD('Running for databse: ' || l_db_name);

  oe_debug_pub.add('Processing line info ....');
  FOR c IN lines
  LOOP
    l_line_id := TO_CHAR(c.line_id);
    ---
    UPDATE oe_ordeR_lines_all
       SET flow_status_code = 'AWAITING_SHIPPING',
           last_updated_by    = -1,
             last_update_date    = SYSDATE
     WHERE line_id = c.line_id;
    ---
    oe_debug_pub.add('Seting Context for line ' || c.line_id);
    Oe_standard_wf.OEOL_Selector(p_itemtype => 'OEOL', p_itemkey => l_line_id, p_actid => 12345, p_funcmode => 'SET_CTX', p_result => l_result);
    oe_debug_pub.add('Result: '||l_result );
    ---
    oe_debug_pub.add('Calling handleerror for Line...');
    --******* Set this flag to Y if Ship_line activity should be skipped without any checking
    --OE_SHIPPING_WF.G_DEV_SKIP := 'Y';
    wf_engine.HandleError('OEOL',l_line_id, 'SHIP_LINE', 'RETRY', NULL);
  END LOOP;
  Oe_debug_pub.debug_OFF;
  NULL;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(' Error in the base script : '||sqlerrm);
  oe_debug_pub.add('### Error in the base script : '||sqlerrm);
  Oe_debug_pub.debug_OFF;
END;
/
spool OFF;
COMMIT;

Internal Requisition Interface

When working with Internal Order Process, I had met the following problem:
1) Created Internal Requisition and Approved it.
2) Ran “Create Internal Order” concurrent request
3) Ran “Order Import” concurrent request in Oracle Order Management module.
4) Navigated to Order Organizer  to find Internal Sales Order that created, but can not found.
I had do step by step below to track and fix this issue:

a)Check the data of Internal Requisition in interface table, I use these SQL:

SELECT orig_sys_document_ref
  FROM oe_headers_iface_all
 WHERE order_source_id = 10
   AND orig_sys_document_ref IN (SELECT requisition_header_id
                                   FROM po_requisition_headers_all
                                  WHERE segment1 = '&Internal_Req_num');

SELECT orig_sys_document_ref
  FROM oe_lines_iface_all
 WHERE order_source_id = 10
   AND orig_sys_document_ref IN (SELECT requisition_header_id
                                   FROM po_requisition_headers_all
                                  WHERE segment1 = '&Internal_Req_num');

After run those SQL, the result is no row, so there is no date on interface table

b)I run the following SQL to check the Internal Requisition already import to Sales Order or not?

SELECT 'Exists in OM main'
  FROM oe_order_headers_all
 WHERE order_source_id = 10
   AND source_document_id IN (SELECT requisition_header_id
                                FROM po_requisition_headers_all
                               WHERE segment1 = '&Internal_Req_num');

SELECT porh.segment1 purchase_req, ooh.order_number sales_order,
       ool.line_number so_line_no, porl.line_num pur_req_line_no
  FROM oe_order_headers_all ooh,
       po_requisition_headers_all porh,
       po_requisition_lines_all porl,
       po_req_distributions_all pord,
       oe_order_lines_all ool,
       po_system_parameters_all posp
 WHERE ooh.order_source_id = posp.order_source_id
   AND porh.org_id = posp.org_id
   AND porh.requisition_header_id = ool.source_document_id
   AND porl.requisition_line_id = ool.source_document_line_id
   AND porh.requisition_header_id = porl.requisition_header_id
   AND porl.requisition_line_id = pord.requisition_line_id
   AND ooh.org_id = posp.org_id
   AND ool.header_id = ooh.header_id
   AND ool.orig_sys_line_ref NOT LIKE '%OE_ORDER_LINES_ALL%'
   AND ool.source_document_line_id IS NOT NULL
   AND porh.segment1 = '&Internal_Req_num';

Its still no record, that mean this Internal Requisition has not been imported and not in interface table yet.

c)I don’t know why this internal requisition can not import but the transfer flag is set to ‘Y’. I tried to update this Flag and run process again (Recommend do this in Test Instance first):

UPDATE po_requisition_headers_all
   SET transferred_to_oe_flag = 'N'
 WHERE requisition_header_id = '&req_header_id';

UPDATE po_requisition_lines_all
   SET transferred_to_oe_flag = 'N'
 WHERE requisition_header_id = '&req_header_id';

Ran the ‘Create Internal Orders’ again.
Ran the ‘Order Import Process’ then check the Sales Order had been created.
No related posts.

Internal Requisition Interface

When working with Internal Order Process, I had met the following problem:
1) Created Internal Requisition and Approved it.
2) Ran “Create Internal Order” concurrent request
3) Ran “Order Import” concurrent request in Oracle Order Management module.
4) Navigated to Order Organizer  to find Internal Sales Order that created, but can not found.
I had do step by step below to track and fix this issue:

a)Check the data of Internal Requisition in interface table, I use these SQL:

SELECT orig_sys_document_ref
  FROM oe_headers_iface_all
 WHERE order_source_id = 10
   AND orig_sys_document_ref IN (SELECT requisition_header_id
                                   FROM po_requisition_headers_all
                                  WHERE segment1 = '&Internal_Req_num');

SELECT orig_sys_document_ref
  FROM oe_lines_iface_all
 WHERE order_source_id = 10
   AND orig_sys_document_ref IN (SELECT requisition_header_id
                                   FROM po_requisition_headers_all
                                  WHERE segment1 = '&Internal_Req_num');

After run those SQL, the result is no row, so there is no date on interface table

b)I run the following SQL to check the Internal Requisition already import to Sales Order or not?

SELECT 'Exists in OM main'
  FROM oe_order_headers_all
 WHERE order_source_id = 10
   AND source_document_id IN (SELECT requisition_header_id
                                FROM po_requisition_headers_all
                               WHERE segment1 = '&Internal_Req_num');

SELECT porh.segment1 purchase_req, ooh.order_number sales_order,
       ool.line_number so_line_no, porl.line_num pur_req_line_no
  FROM oe_order_headers_all ooh,
       po_requisition_headers_all porh,
       po_requisition_lines_all porl,
       po_req_distributions_all pord,
       oe_order_lines_all ool,
       po_system_parameters_all posp
 WHERE ooh.order_source_id = posp.order_source_id
   AND porh.org_id = posp.org_id
   AND porh.requisition_header_id = ool.source_document_id
   AND porl.requisition_line_id = ool.source_document_line_id
   AND porh.requisition_header_id = porl.requisition_header_id
   AND porl.requisition_line_id = pord.requisition_line_id
   AND ooh.org_id = posp.org_id
   AND ool.header_id = ooh.header_id
   AND ool.orig_sys_line_ref NOT LIKE '%OE_ORDER_LINES_ALL%'
   AND ool.source_document_line_id IS NOT NULL
   AND porh.segment1 = '&Internal_Req_num';

Its still no record, that mean this Internal Requisition has not been imported and not in interface table yet.

c)I don’t know why this internal requisition can not import but the transfer flag is set to ‘Y’. I tried to update this Flag and run process again (Recommend do this in Test Instance first):

UPDATE po_requisition_headers_all
   SET transferred_to_oe_flag = 'N'
 WHERE requisition_header_id = '&req_header_id';

UPDATE po_requisition_lines_all
   SET transferred_to_oe_flag = 'N'
 WHERE requisition_header_id = '&req_header_id';

Ran the ‘Create Internal Orders’ again.
Ran the ‘Order Import Process’ then check the Sales Order had been created.
No related posts.

Payroll - Element Updation ( Basic/HRA etc)

STEP I  -- Insert into XYKA_SALARY_API

STEP II -- Call Procedure import_records

        -- Records will be populated into xyka_salary_proposal
       
STEP III  -- process_salary_proposal_api ( Main call to hr_maintain_proposal_api.insert_salary_proposal )

CREATE SEQUENCE XYKA_SALARY_PROPOSAL_IDS
  START WITH 1000
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

CREATE TABLE XYKA_SALARY_PROPOSAL
(
  X_PROPOSAL_ID      NUMBER,
  X_EMPLOYEE_NUMBER  NUMBER,
  X_EFFECTIVE_DATE   DATE,
  X_NET_AMOUNT       NUMBER,
  X_PROCESS_FLAG     CHAR(1 BYTE),
  X_APPROVED_FLAG    CHAR(1 BYTE),
  X_REMARKS          VARCHAR2(240 BYTE),
  CREATION_DATE      DATE,
  CREATED_BY         NUMBER,
  LAST_UPDATE_DATE   DATE,
  LAST_UPDATED_BY    NUMBER,
  LAST_UPDATE_LOGIN  NUMBER
);


CREATE TABLE XYKA_SALARY_API
(
  X_EMPLOYEE_NUMBER  NUMBER,
  X_EFFECTIVE_DATE   DATE,
  X_NET_AMOUNT       NUMBER,
  X_TRANSFER_DATE    DATE,
  X_REMARKS          VARCHAR2(240 BYTE)
)

select * from XYKA_SALARY_PROPOSAL  

--drop table xyka.XYKA_SALARY_PROPOSAL 

commit

-------------------------------------------------

select ELEMENT_NAME , REPORTING_NAME , decode (PROCESSING_TYPE ,'R','REC','N','NREC') from PAY_ELEMENT_TYPES_F

select * from PAY_ELEMENT_TYPES_F 

CREATE OR REPLACE FORCE VIEW APPS.XYKA_SALARY_PROPOSAL_V
(
   SALARY_API_ROWID,
   PROPOSAL_ID,
   EMPLOYEE_NUMBER,
   EMPLOYEE_NAME,
   BUSINESS_GROUP_ID,
   GRADE,
   ASSIGNMENT_ID,
   ELEMENT_ENTRY_ID,
   DEPARTMENT,
   EFFECTIVE_DATE,
   PERIOD_NAME,
   NET_AMOUNT,
   REMARKS,
   APPROVED_FLAG,
   PROCESS_FLAG,
   CREATION_DATE,
   CREATED_BY,
   LAST_UPDATE_DATE,
   LAST_UPDATED_BY,
   LAST_UPDATE_LOGIN
)
AS
   SELECT   ROWID salary_api_rowid,
            xsp.x_proposal_id proposal_id,
            xsp.x_employee_number employee_number,
            (SELECT   last_name
               FROM   per_people_f
              WHERE   employee_number = TO_CHAR (xsp.x_employee_number)
                      AND ROWNUM = 1)
               employee_name,
            (SELECT   business_group_id
               FROM   per_people_f
              WHERE   employee_number = TO_CHAR (xsp.x_employee_number)
                      AND ROWNUM = 1)
               business_group_id,
            (SELECT   pg.NAME
               FROM   per_people_f ppf, per_assignments_f paf, per_grades pg
              WHERE   ppf.person_id = paf.person_id
                      AND paf.grade_id = pg.grade_id
                      AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                   AND  ppf.effective_end_date
                      AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                   AND  paf.effective_end_date
                      AND (pg.date_to IS NULL
                           OR xsp.x_effective_date BETWEEN pg.date_from
                                                       AND  pg.date_to)
                      AND ppf.employee_number =
                            TO_CHAR (xsp.x_employee_number))
               grade,
            (SELECT   paf.assignment_id
               FROM   per_people_f ppf, per_assignments_f paf
              WHERE   ppf.person_id = paf.person_id
                      AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                   AND  ppf.effective_end_date
                      AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                   AND  paf.effective_end_date
                      AND ppf.employee_number =
                            TO_CHAR (xsp.x_employee_number))
               assignment_id,
            (SELECT   element_entry_id
               FROM   pay_element_entries_f
              WHERE   assignment_id =
                         (SELECT   paf.assignment_id
                            FROM   per_people_f ppf, per_assignments_f paf
                           WHERE   ppf.person_id = paf.person_id
                                   AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                                AND  ppf.effective_end_date
                                   AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                                AND  paf.effective_end_date
                                   AND ppf.employee_number =
                                         TO_CHAR (xsp.x_employee_number))
                      AND SYSDATE BETWEEN effective_start_date
                                      AND  effective_end_date
                      AND element_type_id =
                            (SELECT   element_type_id
                               FROM   pay_element_types_f_tl
                              WHERE   element_name = 'Basic'
                                      AND element_type_id = 92))  --change element as per requirement
               element_entry_id,
            (SELECT   NAME
               FROM   hr_all_organization_units_tl
              WHERE   organization_id =
                         (SELECT   organization_id
                            FROM   per_people_f ppf, per_assignments_f paf
                           WHERE   ppf.person_id = paf.person_id
                                   AND xsp.x_effective_date BETWEEN ppf.effective_start_date
                                                                AND  ppf.effective_end_date
                                   AND xsp.x_effective_date BETWEEN paf.effective_start_date
                                                                AND  paf.effective_end_date
                                   AND ppf.employee_number =
                                         TO_CHAR (xsp.x_employee_number)))
               department,
            xsp.x_effective_date effective_date,
            TO_CHAR (xsp.x_effective_date, 'MON-RR') period_name,
            xsp.x_net_amount net_amount,
            xsp.x_remarks remarks,
            xsp.x_approved_flag approved_flag,
            xsp.x_process_flag process_flag,
            xsp.creation_date,
            xsp.created_by,
            xsp.last_update_date,
            xsp.last_updated_by,
            xsp.last_update_login
     FROM   xyka_salary_proposal xsp;
    
---------------------------------------------------------------------

CREATE OR REPLACE PACKAGE APPS.xyka_salary_proposal_api
AS
--## ##################################################################
--#
--# Script        :   xyka_salary_api.sql
--# Author        :   Rushikesh Ranade
--# System        :   YK Almoayyed and Sons
--# Date          :   12 Jul 2012 11:54
--# Version       :   1.0
--# Description   :   Customized Package to process HR Salary APIs
--#
--# Modif Hist :
--# -------------
--#
--# Usage Instructions:
--# -------------------
--# The Objects has to be called from the Salary API Form and Salary
--# API Requests
--#
--## ##################################################################

   /* Package Objects Definition */
-- ---------------------------------------------------
-- Procedure validate the data given in the Salary API
-- table and populates the validated data into the
-- Salary API Main tables
-- populated based on the supplied parameters
-- the parameters are
-- p_period specifies the Period Name
-- p_user_id specifies the User ID
-- p_login_id specifies the User Login ID
-- l_status specifies the Process Status
-- L_message specifies the processing messages
-- ------------------
   PROCEDURE import_records (
      p_out1       OUT      VARCHAR2,
      p_out2       OUT      VARCHAR2,
      p_period     IN       VARCHAR2,
      p_user_id    IN       NUMBER,
      p_login_id   IN       NUMBER
   );

-- ------------------------------------------------
-- Procedure to process the Salary proposal API
-- based on the supplied parameters
-- p_period_name specifies the Period Name
-- ---------------
   PROCEDURE process_salary_proposal_api (p_period_name IN VARCHAR2);
END xyka_salary_proposal_api;

--------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY APPS.xyka_salary_proposal_api
AS
-- ---------------------------------------------------
-- Procedure validate the data given in the Salary API
-- table and populates the validated data into the
-- Salary API Main tables
-- populated based on the supplied parameters
-- the parameters are
-- p_period specifies the Period Name
-- p_user_id specifies the User ID
-- p_login_id specifies the User Login ID
-- ------------------
   PROCEDURE import_records (
      p_out1       OUT      VARCHAR2,
      p_out2       OUT      VARCHAR2,
      p_period     IN       VARCHAR2,
      p_user_id    IN       NUMBER,
      p_login_id   IN       NUMBER
   )
   IS
      /* Local Variables */
      l_success_count   NUMBER         := 0;
      l_rec_count       NUMBER         := 0;
      l_status          VARCHAR2 (20);
      l_message         VARCHAR2 (240);

      CURSOR salary_api (c_period_name IN VARCHAR2)
      IS
         SELECT x_employee_number, x_effective_date, x_net_amount
           FROM xyka_salary_api
          WHERE TO_CHAR (x_effective_date, 'MON-RR') = c_period_name
            AND x_transfer_date IS NULL;
   BEGIN
      /* Transfers the records into the Salary API Tables */
      fnd_file.put_line
         (fnd_file.output,
             'YKA - Upload Salary Proposal for a Period (XYKASALP) Started on : '
          || TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS')
         );
      fnd_file.put_line
         (fnd_file.output,
          '-----------------------------------------------------------------------------'
         );
      fnd_file.put_line (fnd_file.output, '         ');

      FOR r IN salary_api (p_period)
      LOOP
         fnd_file.put_line (fnd_file.output,
                               'Processing for Employee Number # '
                            || r.x_employee_number
                           );
         /* Re-Sets the Variables */
         l_status := NULL;
         l_message := NULL;
         l_rec_count := l_rec_count + 1;

         /* Start populating the Salary API Main table */
         BEGIN
            INSERT INTO xyka_salary_proposal
                        (x_proposal_id,
                         x_employee_number, x_effective_date,
                         x_net_amount, x_process_flag, x_approved_flag,
                         x_remarks, creation_date, created_by,
                         last_update_date, last_updated_by, last_update_login
                        )
                 VALUES (xyka_salary_proposal_ids.NEXTVAL,
                         r.x_employee_number, r.x_effective_date,
                         r.x_net_amount, 'N', 'N',
                         NULL, SYSDATE, p_user_id,
                         SYSDATE, p_user_id, p_login_id
                        );
         EXCEPTION
            WHEN OTHERS
            THEN
               l_status := 'ERROR';
               l_message := SQLERRM;
               fnd_file.put_line (fnd_file.output,
                                  ' *** Error Message *** : ' || SQLERRM
                                 );
         END;

         /* If any error occured while transferring the record */
         IF l_status = 'ERROR'
         THEN
            /* Updates the Base Table with the error message */
            UPDATE xyka_salary_api
               SET x_remarks = l_message
             WHERE x_employee_number = r.x_employee_number
               AND TO_CHAR (x_effective_date, 'MON-RR') = p_period;
         ELSIF l_status IS NULL
         THEN
            l_success_count := l_success_count + 1;

            /* If no processing errors occurred then, updates the Transfer Date */
            UPDATE xyka_salary_api
               SET x_remarks = 'TRANSFERRED',
                   x_transfer_date = SYSDATE
             WHERE x_employee_number = r.x_employee_number
               AND TO_CHAR (x_effective_date, 'MON-RR') = p_period;
         END IF;

         /* Commits the Changes */
         COMMIT;
      END LOOP;

      fnd_file.put_line (fnd_file.output, '         ');
      fnd_file.put_line (fnd_file.output,
                            'Total Records processed by the Upload program : '
                         || l_rec_count
                        );
      fnd_file.put_line (fnd_file.output,
                            'Total Records Uploaded by the Upload program : '
                         || l_success_count
                        );
      fnd_file.put_line (fnd_file.output, '         ');
      fnd_file.put_line
         (fnd_file.output,
             'YKA - Upload Salary Proposal for a Period (XYKASALP) Completed on : '
          || TO_CHAR (SYSDATE, 'DD-MON-RRRR HH24:MI:SS')
         );
      fnd_file.put_line
         (fnd_file.output,
          '-----------------------------------------------------------------------------'
         );
   END;

  -- ------------------------------------------------
-- Procedure to process the Salary proposal API
-- based on the supplied parameters
-- p_period_name specifies the Period Name
-- ---------------
   PROCEDURE process_salary_proposal_api (p_period_name IN VARCHAR2)
   IS
      CURSOR salary_api (c_period_name IN VARCHAR2)
      IS
         SELECT proposal_id, employee_number, net_amount, effective_date,
                business_group_id, assignment_id, element_entry_id
           FROM xyka_salary_proposal_v
          WHERE TO_CHAR (effective_date, 'MON-RR') = c_period_name
            AND approved_flag = 'Y'
            AND process_flag = 'N';

      /* Local Variables */
      l_row_id                       VARCHAR2 (30);
      l_pay_proposal_id              NUMBER;
      l_element_entry_id             NUMBER;
      l_ovn                          NUMBER;
      l_inv_next_sal_date_warning    BOOLEAN;
      l_inv_next_perf_date_warning   BOOLEAN;
      l_proposed_salary_warning      BOOLEAN;
      l_approved_warning             BOOLEAN;
      l_payroll_warning              BOOLEAN;
      l_dummy                        BOOLEAN;
      l_approved                     VARCHAR2 (1);
      l_status                       VARCHAR2 (20);
      l_message                      VARCHAR2 (240);
   BEGIN
      FOR r IN salary_api (p_period_name)
      LOOP
         l_status := NULL;
         l_message := NULL;
     l_element_entry_id:=NULL;

         BEGIN
            /* Start processing the API */
            hr_maintain_proposal_api.insert_salary_proposal
                 (p_pay_proposal_id                => l_pay_proposal_id,
                  p_assignment_id                  => r.assignment_id,
                  p_business_group_id              => r.business_group_id,
                  p_change_date                    => r.effective_date,
                  p_comments                       => NULL,
                  p_next_sal_review_date           => NULL,
                  p_proposal_reason                => NULL,
                  p_proposed_salary_n              => r.net_amount,
                  p_forced_ranking                 => NULL,
                  p_performance_review_id          => NULL,
                  p_attribute_category             => NULL,
                  p_attribute1                     => NULL,
                  p_attribute2                     => NULL,
                  p_attribute3                     => NULL,
                  p_attribute4                     => NULL,
                  p_attribute5                     => NULL,
                  p_attribute6                     => NULL,
                  p_attribute7                     => NULL,
                  p_attribute8                     => NULL,
                  p_attribute9                     => NULL,
                  p_attribute10                    => NULL,
                  p_attribute11                    => NULL,
                  p_attribute12                    => NULL,
                  p_attribute13                    => NULL,
                  p_attribute14                    => NULL,
                  p_attribute15                    => NULL,
                  p_attribute16                    => NULL,
                  p_attribute17                    => NULL,
                  p_attribute18                    => NULL,
                  p_attribute19                    => NULL,
                  p_attribute20                    => NULL,
                  p_object_version_number          => l_ovn,
                  p_multiple_components            => 'N',
                  p_approved                       => 'Y',
                  p_validate                       => FALSE,
                  p_element_entry_id               => r.element_entry_id,
                  p_inv_next_sal_date_warning      => l_inv_next_sal_date_warning,
                  p_proposed_salary_warning        => l_proposed_salary_warning,
                  p_approved_warning               => l_approved_warning,
                  p_payroll_warning                => l_payroll_warning
                 );
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               /* Catches the Exceptions */
               l_message := SQLERRM;
               l_status := 'ERROR';
         END;

         /* If any error occured then */
         IF l_status IS NOT NULL
         THEN
            UPDATE xyka_salary_proposal
               SET x_remarks = l_message,
                   x_approved_flag = 'N',
                   x_process_flag = 'N'
             WHERE x_proposal_id = r.proposal_id;

            COMMIT;
         /* If the proposal is processed then */
         ELSIF l_status IS NULL
         THEN
            UPDATE xyka_salary_proposal
               SET x_remarks = 'PROCESSED',
                   x_process_flag = 'Y'
             WHERE x_proposal_id = r.proposal_id;

            COMMIT;
         END IF;
      END LOOP;
   END process_salary_proposal_api;
END xyka_salary_proposal_api;
/
------------------ Execute --------------
INSERT INTO XYKA_SALARY_API (X_EMPLOYEE_NUMBER,X_EFFECTIVE_DATE,X_NET_AMOUNT,X_TRANSFER_DATE,X_REMARKS)VALUES(14699,TO_DATE (SYSDATE - 14, 'DD-MM-YY'),1200,NULL,'5% salary hike JUL-12');

 select to_date(sysdate-14,'DD-MM-YY') from dual

 commit 


 select * from XYKA_SALARY_API

 delete from XYKA_SALARY_API
 --edit XYKA_SALARY_API

 SELECT x_employee_number, x_effective_date, x_net_amount
           FROM xyka_salary_api
          WHERE TO_CHAR (x_effective_date, 'MON-RR') = :c_period_name
            AND x_transfer_date IS NULL
           
select * from fnd_user where user_name = '99999'
                
------------------ Procedure 1 -----------------------                 

DECLARE
   p_out1       VARCHAR2 (240);
   p_out2       VARCHAR2 (240);
   p_period     VARCHAR2 (10) := 'JUL-12';
   p_user_id    number := 1295;
   p_login_id   number := 1295;
BEGIN
   xyka_salary_proposal_api.import_records (p_out1,
                                            p_out2,
                                            p_period,
                                            p_user_id,
                                            p_login_id);
END;           

commit

-- Record gets added into

select * from xyka_salary_proposal

delete from xyka_salary_proposal  where X_APPROVED_FLAG = 'Y'

-- on success x_remark gets updated to TRANSFERRED  and trnsfer_date to SYSDATE

select * from xyka_salary_api

UPDATE   xyka_salary_proposal
   SET   X_APPROVED_FLAG = 'Y' ,
         X_EFFECTIVE_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY'),
         LAST_UPDATE_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY'),
         CREATION_DATE = TO_DATE ('01-07-2012', 'DD-MM-YYYY')
 --WHERE   X_EMPLOYEE_NUMBER = 14699

commit ;

select * from xyka_salary_proposal

--delete from xyka_salary_proposal where X_REMARKS  like 'ORA-20001: This date already exists. Please enter a new date.'


SELECT proposal_id, employee_number, net_amount, effective_date,
                business_group_id, assignment_id, element_entry_id
           FROM xyka_salary_proposal_v
          WHERE TO_CHAR (effective_date, 'MON-RR') = :c_period_name
            AND approved_flag = 'Y'
            AND process_flag = 'N';
           
           
-- call process_salary_proposal_api

DECLARE
   p_period_name   VARCHAR2 (10) := 'JUL-22';
BEGIN
   xyka_salary_proposal_api.process_salary_proposal_api (p_period_name);
END;

COMMIT

/* If any error occured then */
        x_approved_flag = 'N',
        x_process_flag = 'N'
       
ORA-20001: The effective date for all DateTracked operations must not include a time component.

/* If the proposal is processed then */
        x_remarks = 'PROCESSED',
        x_process_flag = 'Y'

select * from xyka_salary_proposal

select * from XYKA_SALARY_PROPOSAL_V

PO - Action History

********** PO - Action History **********

  SELECT   pah.object_id,
           pah.object_type_code,
           pah.object_sub_type_code,
           pah.sequence_num,
           pah.last_update_date,
           pah.last_updated_by,
           pah.creation_date,
           pah.created_by,
           pah.action_code,
           plc1.displayed_field Action,
           pah.action_date,
           pah.employee_id,
           hr.full_name Employee,
           pah.approval_path_id,
           pah.note,
           pah.object_revision_num,
           pah.offline_code,
           pah.last_update_login,
           pah.request_id,
           pah.program_application_id,
           pah.program_id,
           pah.program_update_date,
           pah.program_date,
           pah.approval_group_id
    FROM   po_action_history pah,
           po_lookup_codes plc1,
           po_lookup_codes plc2,
           per_all_people_f hr
   WHERE       pah.object_id = 176977  -- po_header_id
           AND pah.object_type_code <> 'RELEASE'
           AND pah.object_sub_type_code = 'STANDARD'
           AND hr.person_id = pah.employee_id
           AND pah.action_date BETWEEN hr.effective_start_date
                                   AND  hr.effective_end_date
           AND plc1.lookup_code(+) = pah.action_code
           AND plc1.lookup_type(+) = 'APPROVER ACTIONS'
           AND plc2.lookup_code(+) = pah.action_code
           AND plc2.lookup_type(+) = 'CONTROL ACTIONS'
ORDER BY   pah.sequence_num


********* PO - Approval Hierarchy *******
SELECT   det.lev "Level",
         det.subordinate_POSITION_ID "Subordinate Position Id",
         det.NAME "Name",
         DECODE (
            (SELECT   COUNT ( * ) cnt
               FROM   PER_ALL_ASSIGNMENTS_F paa
              WHERE   paa.position_id = det.subordinate_POSITION_ID
                      AND SYSDATE BETWEEN paa.effective_start_date
                                      AND  NVL (paa.effective_end_date,
                                                SYSDATE)),
            1,
            (SELECT   full_name
               FROM   PER_ALL_ASSIGNMENTS_F paa, per_all_people_f pp
              WHERE   paa.position_id = det.subordinate_POSITION_ID
                      AND SYSDATE BETWEEN paa.effective_start_date
                                      AND  NVL (paa.effective_end_date,
                                                SYSDATE)
                      AND SYSDATE BETWEEN pp.effective_start_date
                                      AND  NVL (pp.effective_end_date,
                                                SYSDATE)
                      AND paa.PERSON_ID = pp.person_id),
            'Multiple Employees attached to this Position'
         )
            Employee_Name
  FROM   (SELECT   ps.lev, PS.subordinate_POSITION_ID, pos.NAME
            FROM   (    SELECT   LEVEL lev, PS.subordinate_POSITION_ID
                          FROM   per_pos_structure_elements PS
                    START WITH   PS.POS_STRUCTURE_VERSION_ID = NVL (62, 0)
                                 AND PS.SUBORDINATE_POSITION_ID = 4087
                    CONNECT BY   PRIOR PS.PARENT_POSITION_ID =
                                    PS.SUBORDINATE_POSITION_ID
                                 AND PS.POS_STRUCTURE_VERSION_ID =
                                       NVL (62, 0)) ps,
                   per_positions POS
           WHERE   ps.subordinate_POSITION_ID = pos.POSITION_ID) det

********* PO - AP *********

SELECT   ih.invoice_num "Invoice Num",
         pla.line_num "PO Line Num",
         plla.shipment_num "PO Shipment Num",
         plla.quantity_billed "Shipment Qty Billed"
  FROM   ap_invoices ih,
         ap_invoice_distributions id,
         po_headers ph,
         po_distributions pd,
         po_lines pla,
         po_line_locations plla
 WHERE       ih.invoice_id(+) = id.invoice_id
         AND id.po_distribution_id(+) = pd.po_distribution_id
         AND ph.po_header_id = pd.po_header_id
         AND ph.po_header_id = pla.po_header_id
         AND pla.po_line_id = plla.po_line_id
         AND plla.line_location_id = pd.line_location_id
         AND ih.invoice_num IS NOT NULL
         AND NVL (plla.po_release_id, 0) = 0
         AND ph.po_header_id = 176977

R12 BANK TABLE AND QUERY

R12 BANK TABLE AND QUERY

SELECT    HOU.NAME OPERATING_UNIT,
                  BNK.BANK_ACCOUNT_NUM,
                  BRNCH.BANK_NAME,
                  BRNCH.BANK_BRANCH_TYPE,
                  BRNCH.BANK_BRANCH_NAME,
                  BRNCH.DESCRIPTION
  FROM   CE_BANK_ACCOUNTS BNK,
         CE_BANK_ACCT_USES_ALL SITE,
         CEFV_BANK_BRANCHES BRNCH,
         HR_OPERATING_UNITS HOU
 WHERE       BNK.BANK_ACCOUNT_ID = SITE.BANK_ACCOUNT_ID
         AND BRNCH.BANK_BRANCH_ID = BNK.BANK_BRANCH_ID
         AND HOU.ORGANIZATION_ID = SITE.ORG_ID 

==========================================================================


SELECT   HOU.NAME OPERATING_UNIT,
         sob.SHORT_NAME sob_name,
         BRNCH.BANK_NAME,                                  
         BRNCH.BANK_BRANCH_NAME,                   
         BNK.DESCRIPTION,
         BRNCH.ADDRESS_LINE1,
         BRNCH.ADDRESS_LINE2,
         BRNCH.ADDRESS_LINE3,
         BRNCH.ADDRESS_LINE4,
         BRNCH.CITY,
         BRNCH.STATE,
         BRNCH.ZIP_CODE,
         BRNCH.COUNTRY,
         BNK.BANK_ACCOUNT_NAME,
         BNK.BANK_ACCOUNT_TYPE,
         BNK.BANK_ACCOUNT_NUM,
         BNK.MULTI_CURRENCY_ALLOWED_FLAG,
         BNK.MULTI_CURRENCY_ALLOWED_FLAG,
         BNK.ACCOUNT_CLASSIFICATION,
         BNK.ZERO_AMOUNT_ALLOWED,
         BNK.MAX_CHECK_AMOUNT,
         BNK.RECEIPT_MULTI_CURRENCY_FLAG,
         BNK.CURRENCY_CODE,
         BNK.ACCOUNT_HOLDER_NAME,
         gcf.CONCATENATED_SEGMENTS
  FROM   CE_BANK_ACCOUNTS BNK,
         CE_BANK_ACCT_USES_ALL SITE,
         CEFV_BANK_BRANCHES BRNCH,
         HR_OPERATING_UNITS HOU,
         gl_sets_of_books sob,
         gl_code_combinations_kfv gcf
 WHERE       BNK.BANK_ACCOUNT_ID = SITE.BANK_ACCOUNT_ID
         AND BRNCH.BANK_BRANCH_ID = BNK.BANK_BRANCH_ID
         AND HOU.ORGANIZATION_ID = SITE.ORG_ID
         AND sob.SET_OF_BOOKS_ID = HOU.SET_OF_BOOKS_ID
         AND BNK.ASSET_CODE_COMBINATION_ID = gcf.CODE_COMBINATION_ID

PO AP R12

SELECT PO_DISTRIBUTION_ID,SUM(AMOUNT),SUM(QUANTITY_INVOICED)
FROM AP_INVOICE_DISTRIBUTIONS_ALL
GROUP BY PO_DISTRIBUTION_ID
HAVING PO_DISTRIBUTION_ID IN
(
SELECT DISTINCT(PO_DISTRIBUTION_ID) FROM
AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = &INVOICE_ID
)