Saturday, July 28, 2012

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

Tuesday, July 10, 2012

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

Monday, June 18, 2012

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

Wednesday, June 13, 2012

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
)

Monday, April 16, 2012

Inventory - Onhand , Transaction Quantity , Reserve Quantity

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

create table  xyka_test_todrop_bkp  as ( select * from xyka_test_todrop)

commit

drop table xyka_test_todrop


create table xyka_test_todrop
as
select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM, sum(ON_HAND) ON_HAND
from mtl_onhand_total_v
where ORGANIZATION_ID=105
and SUBINVENTORY_CODE = 'BMD'
group by ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM



alter table xyka_test_todrop
add( qty_transact number,
    qty_reserve number );




select * from xyka_test_todrop

--where item = 'BH-28'

select * from hr_operating_units


DECLARE
x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
x_qoh NUMBER;
x_rqoh NUMBER;
x_qr NUMBER;
x_qs NUMBER;
x_att NUMBER;
x_atr NUMBER;
x_sqoh NUMBER;
x_srqoh NUMBER;
x_sqr NUMBER;
x_sqs NUMBER;
x_satt NUMBER;
x_sqtr NUMBER;
a number := 0;
b number := 0;
BEGIN
inv_globals.set_org_id (85);
inv_quantity_tree_pub.clear_quantity_cache;
for x in (  
    select ORGANIZATION_CODE,ORGANIZATION_ID, SUBINVENTORY_CODE, LOCATOR_ID,LOCATOR,INVENTORY_ITEM_ID,ITEM,ON_HAND
    from xyka_test_todrop
    --where INVENTORY_ITEM_ID = 8367
           --and LOCATOR_ID = 18471
           --and ORGANIZATION_ID = 112
           --and SUBINVENTORY_CODE = 'IBS'
     )
LOOP
inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => x.ORGANIZATION_ID,
p_inventory_item_id => x.INVENTORY_ITEM_ID,
p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => TRUE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => x.SUBINVENTORY_CODE,
p_locator_id => x.LOCATOR_ID,
x_qoh => x_qoh,
x_rqoh => x_rqoh,
x_qr => x_qr,
x_qs => x_qs,
x_att => x_att,
x_atr => x_atr);
update  xyka_test_todrop
set QTY_TRANSACT = X_att,
    qty_reserve = x_atr
where INVENTORY_ITEM_ID = x.INVENTORY_ITEM_ID
           and LOCATOR_ID = x.LOCATOR_ID
           and ORGANIZATION_ID = 105
           and SUBINVENTORY_CODE = 'BMD';
a := sql%rowcount;
b := a + b;          
end loop;
dbms_output.put_line(to_char(b));
END;


select * from xyka_test_todrop

commit

Thursday, April 12, 2012

XML Reports An invalid character was found in text content.

You need to update your profile in 11i / R12 .

change this to UTF8. We had this issue with our international documents .

Go To - System Administrator > Profiles > Find for below profile . change value to - UFT 8

FND: NATIVE CLIENT ENCODING

Before >> 

After >>

Monday, April 9, 2012

WMS - Pick Pending Summery Report

/* Formatted on 4/9/2012 3:12:18 PM (QP5 v5.114.809.3010) */
  SELECT   ROW_NUMBER ()
              OVER (
                 ORDER BY
                    oel.shipping_method_code, hp.party_name, oeh.order_number
              )
              AS sr_no,
           mmtt.transaction_temp_id task_no,
           wdt.task_id,
           hp.party_name customer,
           msi.DESCRIPTION,
           flv.meaning task_status,
           fu1.user_name USER_name,
           mmtt.transaction_quantity QUANTITY,
           oeh.order_number,
           mmtt.SUBINVENTORY_CODE,
           mmtt.TRANSACTION_SOURCE_NAME,
           wt.user_task_type_code EQUIPMENT,
           mmtt.LOCATOR_ID,
           oel.shipping_method_code route,
           ot.name order_type
    FROM   hz_parties hp,
           hz_cust_accounts hca,
           oe_order_headers_all oeh,
           mtl_material_transactions_temp mmtt,
           wms_dispatched_tasks wdt,
           oe_order_lines_all oel,
           fnd_lookup_values flv,
           fnd_user fu1,
           mtl_system_items_b msi,
           oe_transaction_types_tl ot,
           wms_tasks_v wt
   WHERE       msi.organization_id = mmtt.organization_id
           AND msi.inventory_item_id = mmtt.inventory_item_id
           AND oel.line_id = mmtt.trx_source_line_id
           AND flv.lookup_type = 'WMS_TASK_STATUS'
           AND flv.lookup_code =
                 NVL (wdt.status, NVL (mmtt.wms_task_status, 1))
           AND flv.language = 'US'
           AND wdt.transaction_temp_id(+) = mmtt.transaction_temp_id
           AND oeh.header_id = oel.header_id
           AND fu1.employee_id(+) = wdt.person_id
           AND oeh.sold_to_org_id = hca.cust_account_id
           AND hca.party_id = hp.party_id
           AND flv.MEANING IN ('Pending', 'Unreleased')
           AND oeh.order_type_id = ot.transaction_type_id
           AND wt.transaction_temp_id = mmtt.transaction_temp_id
           AND hp.party_name = NVL (:P_CUST, hp.party_name)
           AND wt.user_task_type_code =
                 NVL (:P_EQUIPMENT, wt.user_task_type_code)
           AND oeh.order_number = NVL (:P_ORD_NO, oeh.order_number)
           AND ot.name = NVL (:P_ORD_TYP, ot.name)
           AND ot.language = 'US'
           AND NVL (oel.shipping_method_code, -99) =
                 NVL (:P_ROUTE, NVL (oel.shipping_method_code, -99))
           AND mmtt.SUBINVENTORY_CODE =
                 NVL (:P_SUB_INV, mmtt.SUBINVENTORY_CODE)
           AND TRUNC (mmtt.creation_date) BETWEEN TRUNC(NVL (
                                                           :P_FRM_DT,
                                                           mmtt.creation_date
                                                        ))
                                              AND  TRUNC(NVL (
                                                            :P_TO_DT,
                                                            mmtt.creation_date
                                                         ))
           AND mmtt.organization_id =
                 NVL (:P_ORGANIZATION_ID, mmtt.organization_id)
ORDER BY   oel.shipping_method_code, hp.party_name, oeh.order_number