Friday 28 September 2012

f60gen in R12

frmcmp_batch userid=apps/crpapps module=XXAPINVS.fmb output_file=XXAPINVS.fmx module_type=form batch=no compile_all=yes

HOW TO SET WHO COLUMNS TO FORM

open pre-insert ,
       pre-update,
       pre-delete triggers

using fnd_standard.set_who;

Creating Form Functions in Oracle APPS using API

DECLARE
l_new_menu_id     NUMBER; 
l_last_menu_id    NUMBER; 
l_row_id          VARCHAR2(100); 
l_descr           VARCHAR2(400); 
l_flag_exist_menu NUMBER := 0;
x_function_id     NUMBER; 
x_row_id          ROWID;
x_form_id         NUMBER;
lc_status         varchar2(1);
CURSOR C1
IS
SELECT * FROM
XX_FND_FUNCTIONS
where FUNCTION_NAME NOT in
(select FUNCTION_NAME from FND_FORM_FUNCTIONS);

 BEGIN

   FOR CREC IN C1
    LOOP   
        lc_status := 'Y';
       
        BEGIN
        select fnd_form_functions_s.nextval
          into x_function_id
          from sys.dual;
            Exception
                When others then
                dbms_output.put_line('failed to fnd_form_functions_s');
                lc_status := 'E';
            
          END;
      
       IF CREC.form_id IS NOT NULL THEN
   
        BEGIN
        SELECT  form_id
          into  x_form_id
          FROM  fnd_form
          where form_id=CREC.form_id;
           Exception
                When others then
                dbms_output.put_line('failed to get x_form_id');
                lc_status := 'E';          
          END;
       
       END IF;
       
         if lc_status = 'Y'then
        
         FND_FORM_FUNCTIONS_PKG.INSERT_ROW (
                    X_ROWID => x_row_id,
                    X_FUNCTION_ID => X_FUNCTION_ID,
                    X_WEB_HOST_NAME => null,
                    X_WEB_AGENT_NAME => null,
                    X_WEB_HTML_CALL => CREC.WEB_HTML_CALL,
                    X_WEB_ENCRYPT_PARAMETERS =>'N',
                    X_WEB_SECURED =>'N',
                    X_WEB_ICON =>null,
                    X_OBJECT_ID =>null,
                    X_REGION_APPLICATION_ID =>null,
                    X_REGION_CODE =>null,
                    X_FUNCTION_NAME =>CREC.FUNCTION_NAME, -- find unique function name using query
                    X_APPLICATION_ID =>CREC.APPLICATION_ID, -- find application id using the query for appliaion names and pass 'FND'
                    X_FORM_ID => x_form_id, -- find from id using formname 'XXCO'
                    X_PARAMETERS => CREC.PARAMETERS,
                    X_TYPE => CREC.TYPE,
                    X_USER_FUNCTION_NAME =>CREC.USER_FUNCTION_NAME,
                    X_DESCRIPTION =>CREC.DESCRIPTION,
                    X_CREATION_DATE =>sysdate,
                    X_CREATED_BY => -1,
                    X_LAST_UPDATE_DATE =>sysdate,
                    X_LAST_UPDATED_BY =>-1,
                    X_LAST_UPDATE_LOGIN =>0
                    );
       
         dbms_output.put_line('FUNCTION_NAME '||CREC.FUNCTION_NAME);        
        END IF;   
   END LOOP;
  
   commit;       
       
 end;

Creating Menus in Oracle Apps Using API

DECLARE
l_new_menu_id     NUMBER; 
l_last_menu_id    NUMBER; 
l_row_id          VARCHAR2(100); 
l_descr           VARCHAR2(400); 
l_flag_exist_menu NUMBER := 0;
lc_status         VARCHAR2(1);
v_menu_id         NUMBER;
v_function_id     NUMBER;
v_sub_menu_id     NUMBER;
CURSOR C1
IS
SELECT A.*
FROM   XX_FND_MENU_ENTRIES A,
       FND_MENUS B
WHERE  A.MENU_NAME=B.MENU_NAME
       AND  (MENU_ID,ENTRY_SEQUENCE) NOT IN (SELECT MENU_ID,ENTRY_SEQUENCE FROM FND_MENU_ENTRIES_VL);
 BEGIN
 
  FOR CREC IN C1
  LOOP     
     lc_status := 'Y';
   
        -- get the menu_id
        --
       
            BEGIN
            select menu_id
            into v_menu_id
            from fnd_menus
            where menu_name = CREC.MENU_NAME;
            Exception       
                When others then
                dbms_output.put_line('Invalid Menu' ||CREC.MENU_NAME);
                lc_status := 'E';             
            END;
           
        -- get the menu_id
        --
           IF CREC.function_name IS NOT NULL
           THEN
       
            BEGIN
            select function_id
            into   v_function_id
            from   fnd_form_functions
            where  function_name = CREC.function_name;
            Exception       
                When others then
                dbms_output.put_line('Invalid function_name' || CREC.function_name);
                lc_status := 'E';             
            END;  
           
           END IF;
                     
            -- get the Sub menu_id
        --
              
        IF CREC.SUB_MENU_NAME IS NOT NULL  
         THEN  
         BEGIN
           select menu_id
            into v_sub_menu_id
            from fnd_menus
           where menu_name =CREC.SUB_MENU_NAME;
            Exception       
            When others then
           dbms_output.put_line('Invalid Sub Menu' || CREC.SUB_MENU_NAME);
           lc_status := 'E';             
            END;
           END IF;
          
     
      IF lc_status <> 'E' THEN      
      
     
         select nvl(max(1),0) into l_flag_exist_menu    
             from fnd_menu_entries t    
             where t.menu_id = v_menu_id       
             and ( (v_function_id is null and t.sub_menu_id is null)
             or  t.sub_menu_id = v_sub_menu_id)          
             and ( (v_function_id is null and t.function_id is null)
             or t.function_id = v_function_id)          
             and 1=1;
        dbms_output.put_line('l_flag_exist_menu   '||l_flag_exist_menu);  
        
             if (l_flag_exist_menu = 0) then
        
              FND_MENU_ENTRIES_PKG.INSERT_ROW
              (X_ROWID             => l_row_id,       
               X_MENU_ID           => v_menu_id,      
               X_ENTRY_SEQUENCE    => crec.entry_sequence,
               X_SUB_MENU_ID       => v_sub_menu_id,      
               X_FUNCTION_ID       => v_function_id,      
               X_GRANT_FLAG        => crec.grant_flag,       
               X_PROMPT            => crec.prompt,       
               X_DESCRIPTION       => crec.description,       
               X_CREATION_DATE     => sysdate,       
               X_CREATED_BY        => fnd_global.user_id,       
               X_LAST_UPDATE_DATE  => sysdate,       
               X_LAST_UPDATED_BY   => fnd_global.user_id,       
               X_LAST_UPDATE_LOGIN => fnd_global.login_id);          
       
                dbms_output.put_line('Prompt '||CREC.prompt);
             END IF;         
      
         END IF;
      
   END LOOP;
  
   commit;       
       
 end;

Oracle error -20002: ORA-20002: [WF_NO_ROLE]

Solution

a) Set the Enable Security Groups profile option to Yes at application level for the application relevant to the responsibility
b) Run the CREATE FND_RESP WF ROLES concurrent program from the System Administrator responsibility.

Wednesday 26 September 2012

API - Supplier & Supplier Site Creation


API - Supplier & Supplier Site Creation declare
l_vendor_rec AP_VENDOR_PUB_PKG.r_vendor_rec_type;
l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
x_return_status VARCHAR2(2000);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
l_upd number := 0;
x_vendor_id number;
x_party_id number;
x_vendor_site_id number;
x_party_site_id number;
x_location_id number;
l_party_site_id number;
l_organization_id number;
begin
--mo_global.set_policy_context('S',1650);
l_vendor_rec.SEGMENT1 := '123';
l_vendor_rec.VENDOR_NAME := 'TEST123';
l_vendor_rec.SUMMARY_FLAG := 'N';
l_vendor_rec.ENABLED_FLAG := 'Y';


AP_VENDOR_PUB_PKG.Create_Vendor
( p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_rec => l_vendor_rec,
x_vendor_id => x_vendor_id,
x_party_id => x_party_id
);

if (x_return_status <> 'S') then
dbms_output.put_line('ERROR While supplier creation!!!');
dbms_output.put_line('--------------------------------------');
dbms_output.put_line(x_msg_data);

IF x_msg_count > 1 THEN
FOR i IN 1..x_msg_count LOOP
dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
END LOOP;
END IF;
else



l_vendor_site_rec.vendor_id := x_vendor_id;
l_vendor_site_rec.VENDOR_SITE_CODE := 'LONDON';
l_vendor_site_rec.org_id := 1650;
l_vendor_site_rec.COUNTRY := 'UK';
l_vendor_site_rec.ADDRESS_LINE1 :='London';
l_vendor_site_rec.PURCHASING_SITE_FLAG := 'Y';
l_vendor_site_rec.PAY_SITE_FLAG := 'Y';


AP_VENDOR_PUB_PKG.Create_Vendor_Site
( p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
x_vendor_site_id => x_vendor_site_id,
x_party_site_id => x_party_site_id,
x_location_id => x_location_id
);

if (x_return_status <> 'S') then
dbms_output.put_line(' ERROR while supplier site creation!!!');
dbms_output.put_line('--------------------------------------');
dbms_output.put_line(x_msg_data);
IF x_msg_count > 1 THEN
FOR i IN 1..x_msg_count LOOP
dbms_output.put_line(substr(FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ),1,255));
END LOOP;
END IF;
ELSE
dbms_output.put_line('Supplier Site Created!!!');

end if;
end if;
end;

API - Vendor Site Update

API - Vendor Site Update l_vendor_site_rec AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
l_vendor_site_rec.vendor_id := v_vendor_id; -- Supplier Id
l_vendor_site_rec.org_id := v_org_id; -- Operating Unit id
x_vendor_site_id :=v_site_id; -- Site Id to be updated

l_vendor_site_rec.email_address := 'anilmca02@gmail.com'; -- eg: Email address update

AP_VENDOR_PUB_PKG.Update_Vendor_Site
( p_api_version => 1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_vendor_site_rec => l_vendor_site_rec,
p_vendor_site_id => x_vendor_site_id
);

Wednesday 19 September 2012

Enable Descriptive Flexfields [DFF] in a Custom Forms

Steps for creating DFF in Custom Form


Go to Application Developer
Flex Fields----------Descriptive Flex Fields
1) Select the application: Appropriate Top
2) DFF Name: You’r Choice
3) Title: You’r Choice
4) Description: You’r Choice
5) Table Name: The name which we are using in the Block (We need to create using




Table :EXECUTE ad_dd.register_table('XXTCC', 'XXTBK_OPM_INGRID_BATCH', 'T',8, 10, 90);

Columns
EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE_CATEGORY1',19, 'VARCHAR2', 150, 'N', 'N');
EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE1',20, 'VARCHAR2', 50, 'N', 'N');
EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE2',21, 'VARCHAR2', 50, 'N', 'N');
EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE3',22, 'VARCHAR2', 50, 'N', 'N');

EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE4',23, 'VARCHAR2', 50, 'N', 'N');
EXECUTE ad_dd.register_column('XXTCC', 'XXTBK_OPM_INGRID_BATCH','ATTRIBUTE5',24, 'VARCHAR2', 50, 'N', 'N');



And fill all the above details as below.













Click on Columns Select the list of columns to be displayed in DFF











Next
Segments:Query your DFF












Attach the Value set or leave it as blank

incase user will enter manually.










These are all the steps to be followed to create a DFF in Custom Form.
And next go to forms
The below are two mandatory steps to be followed in FMB1) Create Item ITEM_DFF for which property:
TEXT_ITEM_DESC_FLEX
2) And write the below in code in WHEN_NEW_ITEM_INSTANCE for
the item ITEM_DFF
fnd_flex.event ('WHEN-NEW-ITEM-INSTANCE');
3) Finally in WHEN-NEW_FORM_ INSTANCE we have to call the felx filed structure.

fnd_descr_flex.define ( block => Block name in which the DFF
field => Field from which DFF
appl_short_name => 'XXTCC',
desc_flex_name => TCC_PROD_BATCH_DFF'
);

Tuesday 18 September 2012

Oracle Receivables Deposits API

begin
mo_global.set_policy_context('S','85'); -- pass the org id value
end;
______________________________________________________________________
DECLARE
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(240);
l_count NUMBER;
l_new_trx_number ra_customer_trx.trx_number%type;
l_new_customer_trx_id ra_customer_trx.customer_trx_id%type;
l_new_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%type;
l_new_rowid VARCHAR2(240);
l_new_status VARCHAR2(240);
BEGIN
fnd_global.apps_initialize (user_id => 1438,resp_id =>50801,resp_appl_id => 222);
--Standard API parameters
ar_deposit_api_pub.CREATE_DEPOSIT(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_deposit_date => trunc(sysdate),
p_currency_code => 'GBP',
p_amount => 10000,
p_batch_source_id => 1141,
p_cust_trx_type_id => 1080,
p_class => 'DEP',
p_bill_to_customer_id => 27042,
p_start_date_commitment => sysdate,
p_description => 'Migration Testing',
p_remit_to_address_id => 1060,
p_commit => FND_API.G_TRUE,
X_new_trx_number =>l_new_trx_number,
X_new_customer_trx_id =>l_new_customer_trx_id,
X_new_customer_trx_line_id =>l_new_customer_trx_line_id,
X_new_rowid =>l_new_rowid,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data ) ;
IF l_msg_count = 1 Then
-- There is one message raised by the API, so it has been sent out.
dbms_output.put_line('l_msg_data 'l_msg_data);
ELSIF l_msg_count > 1 Then
--The messages on the stack are more than one, so call them in a loop.
--And print the messages
LOOP
IF nvl(l_count,0) < l_msg_count THEN
l_count := nvl(l_count,0) +1 ;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
IF l_count = 1 THEN
dbms_output.put_line('l_msg_data 1 'l_msg_data);
ELSIF l_count = 2 THEN
dbms_output.put_line('l_msg_data 2 'l_msg_data);
ELSIF l_count = 3 THEN
dbms_output.put_line('l_msg_data 3 'l_msg_data);
ELSIF l_count = 4 THEN
dbms_output.put_line('l_msg_data 4 'l_msg_data);
ELSIF l_count = 5 THEN
dbms_output.put_line('l_msg_data 5 'l_msg_data);
ELSIF l_count = 6 THEN
dbms_output.put_line('l_msg_data 6 'l_msg_data);
END IF;
dbms_output.put_line('l_msg_data 'to_char(l_count)': 'l_msg_data);
ELSE
EXIT;
END IF;
END LOOP;
END IF;
Commit;
END;

Tax Calculation Disable in R12

Please make the following setup changes.
Trading Community Manager Responsibility
Trading Community: Administration > Geography Hierarchy > Query on Country Code > Manage Validations icon > Uncheck Tax Validation boxes > Save

R12 Payment Process Request - Functional and Technical Information

Payment Process Requests
Overview
Under Funds disbursement page, users can submit Payment Process Requests (PPR) to generate payments. There is an option to submit a single Payment Process Request or schedule Payment Process Requests.
There are four steps in the processing of a PPR.
a) Document selection
b) Build Payments
c) Format Payments
d) Confirm Payments
Document selection and Confirm Payments are handled by Payables (AP) code while Build Payments and Format payments are handled by Payments (IBY) code.
Submitting a Single Payment Process Request
Mandatory Fields - Payment process request name, pay through date
Under Payment Attributes tab – Payment Date, Payment Exchange rate type.
Payment Process Profile and Disbursement bank account are optional fields.
Under Processing tab, options are available to stop the process after document selection / payment and also how to create Payment Instruction.
Under Validation Failure Results tab, choose option that best suits the business needs regarding how to handle validation failure on document(s) or payment(s).
Click on Submit to submit the Payment process request.
Document Selection – Payables
Code: AP_AUTOSELECT_PKG
When a Payment Process request is submitted, a record is created in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name which is the same as the payment process request name.
Payment Profile and Internal Bank Account from which payments have to be made do not have to be specified during invoice selection. User who submits the PPR does not need know this information. These values can be provided by at a later stage by a Payments Manager or Administrator.
Selection:
Invoices are then selected based on due date, discount date, paygroup and other criteria provided by the user while submitting the PPR. The selection process is handled by the calling product
The table AP_SELECTED_INVOICES_ALL is populated with selected invoices.
AP_UNSELECTED_INVOICES_ALL is populated with unselected invoices.
Locking:
After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices.
AP_PAYMENT_SCHEDULES_ALL.checkrun_id is populated on the selected documents.
Review:
If the Payment Process Request has been setup to ‘Stop Process for Review After Scheduled Payment Selection’, the process stops for user review. The status of the PPR is set to Invoices Pending Review.
If the ‘Stop Process for Review After Scheduled Payment Selection’ was not enabled, at the end of invoice selection, build program is submitted automatically.
If no invoices met the selection criteria and no payment schedules selected for payment, the PPR is cancelled automatically and the status of the PPR is set to “Cancelled - No Invoices Selected”
If user review required, after the user reviews the selected payment schedules and clicks on Submit, AP calls the IBYBUILD program.
Valid Statuses and actions
At the end of this step, the valid statuses are
a) Invoices Pending Review or
b) Cancelled - No Invoices Selected or
c) Other statuses from missing information such as Missing Exchange rates
If PPR status is Cancelled-No Invoices Selected, there are no valid actions available.
For others, the actions available are
a) Terminate the PPR or
b) Modify / proceed to submit the PPR and start the build process.
Build Payments - Payments
Code: IBY_DISBURSE_SUBMIT_PUB_PKG
Build Payments creates record in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.
Primary Key: PAYMENT_SEVICE_REQUEST_ID
Key Columns:
CALL_AP_PAY_SERVICE_REQ_CODE -> PPR name
CALLING_APP_ID
PAYMENT_SERVICE_REQUEST_STATUS
INTERNAL_BANK_ACCOUNT_ID
MAXIMUM_PAYMENT_AMOUNT
MINIMUM_PAYMENT_AMOUNT
DOCUMENT_REJECTION_LEVEL_CODE
PAYMENT_REJECTION_LEVEL_CODE
REQUIRE_PROP_PMTS_REVIEW_FLAG
CREATE_PMT_INSTRUCTIONS_FLAG
Note: The displayed status of the PPR is generated by ibyvutlb.pls
There is a get_psr_status function that derives the display sttaus of the PPR on the dashboard.
Some of the values for PAYMENT_SERVICE_REQUEST_STATUS in the table are
PAYMENT_SERVICE_REQUEST_STATUS
------------------------------
DOCUMENTS_VALIDATED
INFORMATION_REQUIRED
INSERTED
PAYMENTS_CREATED
PENDING_REVIEW
TERMINATED
VALIDATION_FAILED
The build program populates the IBY_DOCS_PAYABLE_ALL table with the payments. Link to the payment service request table is through PAYMENT_SERVICE_REQUEST_ID.
Key Columns:
Payment_service_request_id
Calling_app_doc_ref_number -> invoice_number
Document_payable_id
Document_status
Payment_currency_code
Payment_amount
Document_amount
Exclusive_payment_flag
Payment_method_code
Payment_id
Formatting_payment_id
Ext_payee_id
Payee_party_id
Payment_profile_id
Internal_bank_account_id
Calling_app_doc_unique_ref2 -> invoice_id
Calling_app_doc_unique_ref3 -> payment number

a) Internal Bank Account/Payment Process Profile Assignment:
Code: IBY_ASSIGN_PUB
If the payment process request has the internal bank account and payment profile assigned to it, the same is assigned to all the documents in the PPR.
If a default internal bank account and PPP were not provided when submitting the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all the documents, the PPR is set to INFORMATION REQUIRED status. The display status of the PPR is “Information Required - Pending Action”
User should complete the missing information and Run Payment Process to continue.
b) Document Validation
Code: IBY_VALIDATIONSETS_PUB
During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations..
b.1 - If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.
b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.
The DOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failure
REQUEST - Reject all documents in this PPR
DOCUMENT - Reject only the document in error
PAYEE - Reject all the documents related to the supplier
NONE - Stop the request for review
b.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.
b.2.2 – DOCUMENT
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
b.2.3 – PAYEE
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
c) Create Payments
Code: IBY_PAYGROUP_PUB
The validated documents are then grouped into proposed payments based on the grouping rules, both user defined and hard coded.
Example: If exclusive_payment_flag = Y on a document, its paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.
Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.
The build program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.
IBY_PAYMENTS_ALL links through payment_service_request_id.
Key Columns:
Payment_service_request_id
Payment_id
Payment_method_code
Payment_status
Payments_complete_flag
Payment_amount,
Dicount_amount_taken
Internal_bank_Account_id
Ext_payee_id
Payment_instruction_id
Payment_profile_id
Void_date
The PAYMENT_REJECTION_LEVEL_CODE can have the following values which determine how the payment processing will continue when there is a validation failure
REQUEST – Reject all payments in the request
PAYMENT – Reject only those payments in error
NONE – Stop the request for review
Request – Entire PPR is rejected. Oracle Payments raises a business event that calls AP to release the documents. The status of the payment process request and proposed payments is updated to ‘REJECTED’.
Payment – Payments that failed validation are rejected and AP releases the documents that belong to the payment that failed validation. The other payments are accepted. The accepted payments get a status of ‘CREATED’.
None – Payments that failed Validation are set to ‘Failed Validation’ and allows for user intervention. Status of the PPR is set to ‘PENDING REVIEW’
If in the PPR setup, ‘Stop Process for Review After Creation of Proposed Payments’ is enabled, the PPR status is set to ‘Pending Proposed Payment Review’. This status prevents further processing until user takes action. If this option to stop for review is not enabled, the status of the PPR is set to ‘Payments Created’. In this status, payment instruction can be created for the PPR.
Format Payments - Payments
Code: IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB
When a PPR is submitted, there are two options
The CREATE_PMT_INSTRUCTIONS_FLAG can be a Y or N
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.
IBY_PAYMENT_INSTRUCTIONS_ALL stores the payment instruction information.
If the PPR is setup to automatically submit instruction, the payment_service_request_id will be populated in iby_payment_instructions_all because the instruction will be specific to the PPR In this case, the instruction can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID
If the PPR processing is setup for the user to submit the instruction as a standard request, then when the instruction is submitted, then the instruction is linked to the PPR through the payments selected by the instruction.
The link in this case will be through iby_payments_all.payment_instruction_id

Key Columns in IBY_PAYMENT_INSTRUCTIONS_ALL
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code
Format:
The following processing occurs during the format step.
a) Number the payments – Check Numbering
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.
Print Checks:
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint
Confirm Payments - Payables
Code: AP_PMT_CALLOUT_PKG
Record Print Status of the checks to confirm the payments. Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments.
This does the following:
a) Assigns sequence/values – Document sequencing.
b) Creates data in AP_CHECKS_ALL with appropriate data from IBY tables.
Checkrun_name = ppr name and checkrun_id = checkrun_id from IBY table.
c) Data inserted into AP_INVOICE_PAYMENTS_ALL for the corresponding checks.
d) AP_PAYMENT_SCHEDULES_ALL for the invoices are updated to indicate the payment details and status.
e) The documents paid in this PPR are released by setting the checkrun_id on the payment schedules to null.
f) AP_INVOICES_ALL is udpated to show payment status
g) Data is deleted from the AP_SELECTED_INVOICES_ALL
h) Data is deleted from AP_UNSELECTED_INVOICES_ALL

Direct Debits In Oracle Apps

DD are an instruction from a customer to their bank authorising an organisation to collect an amount, which may vary directly from their account.
One of the payment methods available in E-Bus along with Cash, Cheque, Credit Card.







Changes in Release 12
Oracle R12 brought some changes in Direct Debit processing and setup.
Create, Approve and Format of Automatic Receipt remain the same
Create, Approve of Remittance Batch are done AR while Formatting process has been moved to Oracle Payment
Bank Data structure are now stored in CE, IBY and HZ tables
How Flag Transaction For DD
Manual: By selecting Direct Debit receipt method and Payment Instrument
Automatic: By passing Payment_trx_extension_id and Receipt_Method during AutoInvoice creation.
Basic Direct Debit Setup in AR
Define Receipt Class and Receipt method with Remittance bank
Define Receipt Source
Assign document sequence to Receipt method defined
Set Profile option: document sequence to Always or Partially Used
Define Payment term
Define Customer Bank Account
Assign Bank account and Receipt method to Customer



R11i and R12 Bank Data Comparisons


Release 12 Release 11i
CE TABLE (Internal Bank Account) AP_BANK_ACCOUNTS_ALL
CE_BANK_ACCOUNTS AP_BANK_BRANCHES
AP_BANK_ACCOUNT_USES_ALL
IBY TABLE (External Bank Account)
IBY_EXT_BANK_ACCOUNTS
IBY_ACCOUNT_OWNERS
HZ TABLE
HZ_PARTIES (Bank id and Bank Name)
HZ_CODE_ASSIGNMENTS (Class Category, Class Code – bank, End date Active)
HZ_ORGANIZATION_PROFILES (bank number, home country code)
HZ_CONTACTS
HZ_RELATIONSHIP (bank and bank branch)

Oracle Employee Signing/Approval Limits

There is no API and other Method To Enter Signing Limits Metalink Note (171837.1)
The only option is direct insert in base table. :)

INSERT INTO ap_web_signing_limits_all
(document_type,
employee_id,
cost_center,
signing_limit,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
org_id
)
VALUES ('APEXP',
123,
r1.cost_centre,
l_sup_amt,
SYSDATE,
gl_user_id,
gl_login_id,
SYSDATE,
gl_user_id,
201
);

Oracle contingent worker APIs

--Create
--
BEGIN
--
hr_contingent_worker_api.create_cwk
(
p_validate => FALSE
,p_start_date => rec_emp_dtls.date_of_hire
,p_business_group_id => 81 -- business group id
,p_last_name => rec_emp_dtls.last_name
,p_known_as => rec_emp_dtls.known_as
,p_middle_names => rec_emp_dtls.middle_names
,p_sex => rec_emp_dtls.gender
,p_person_type_id => 1139 --l_person_type_id
,p_email_address => rec_emp_dtls.email_address
,p_npw_number => rec_emp_dtls.employee_number
,p_first_name => rec_emp_dtls.first_name
,p_national_identifier => rec_emp_dtls.national_identifier
,p_title => rec_emp_dtls.title
,p_date_of_birth => rec_emp_dtls.date_of_birth
,p_original_date_of_hire => l_start_date--rec_emp_dtls.original_date_of_hire
,p_person_id => l_person_id
,p_assignment_id => l_assignment_id
,p_per_object_version_number => l_per_object_version_number
,p_asg_object_version_number => l_asg_object_version_number
,p_per_effective_start_date => l_per_effective_start_date
,p_per_effective_end_date => l_per_effective_end_date
,p_full_name => l_full_name
,p_assignment_sequence => l_assignment_sequence
,p_assignment_number => l_assignment_number
,p_name_combination_warning => l_name_combination_warning
,p_comment_id => l_commnet_id
,p_pdp_object_version_number => l_pdp_object_version_number
);
END;

--Update
--
BEGIN
--
hr_assignment_api.update_cwk_asg_criteria
(
p_validate => FALSE
,p_effective_date => TRUNC(SYSDATE)
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => p_seas_ass_id
,p_called_from_mass_update => FALSE
,p_object_version_number => l_asg_object_version_number
,p_grade_id => NULL
,p_position_id => NULL
,p_job_id => NULL
--
,p_location_id => l_loc_id
,p_organization_id => l_org_id
--
,p_pay_basis_id => NULL
,p_segment1 => NULL
,p_segment2 => NULL
,p_segment3 => NULL
,p_segment4 => NULL
,p_segment5 => NULL
,p_segment6 => NULL
,p_segment7 => NULL
,p_segment8 => NULL
,p_segment9 => NULL
,p_segment10 => NULL
,p_segment11 => NULL
,p_segment12 => NULL
,p_segment13 => NULL
,p_segment14 => NULL
,p_segment15 => NULL
,p_segment16 => NULL
,p_segment17 => NULL
,p_segment18 => NULL
,p_segment19 => NULL
,p_segment20 => NULL
,p_segment21 => NULL
,p_segment22 => NULL
,p_segment23 => NULL
,p_segment24 => NULL
,p_segment25 => NULL
,p_segment26 => NULL
,p_segment27 => NULL
,p_segment28 => NULL
,p_segment29 => NULL
,p_segment30 => NULL
,p_concat_segments => NULL
--
,p_people_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
--
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning
);
END;

Oracle Apps Concurrent program Output as Email

Using the below Unix SH file we can send the Concurrent program Output as Email.Please pass the request id of the Concurrent program and email address.


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


#!/bin/ksh
#################################
# Parameters
# 1 = echo "Start of the Program"
#################################
# File Name #
##############
REQUEST_ID=`echo $*awk '{print $9}' sed 's/"//g'`
REQUEST_ID=`eval "echo $REQUEST_ID"`
echo "Request Id= " ${REQUEST_ID}
###################
# Enail #
###################
mail_add=`echo $* awk '{print $10}' tr -d "`
echo "mail id : ${mail_add}"
req_file='${req_id}.out'
###################
# Data Directory #
###################
cd $APPLCSF/$APPLOUT
uuencode ${REQUEST_ID} ${REQUEST_ID} mailx -v -s"XXX Banks Accounts&PO expense Creation Out File" "$mail_add"

Oracle HRMS Employee LEAVES & Performance Review APIs

--Organization Performance Review
--
BEGIN
HR_PERF_REVIEW_API.CREATE_PERF_REVIEW
( P_VALIDATE => FALSE
,P_PERFORMANCE_REVIEW_ID => l_PERFORMANCE_REVIEW_ID
,P_PERSON_ID => l_person_id
,P_REVIEW_DATE => rec_perf_review.review_date
,P_PERFORMANCE_RATING => rec_perf_review.performance_rating
,P_ATTRIBUTE1 => rec_perf_review.start_date
,P_ATTRIBUTE2 => rec_perf_review.end_date
,P_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER
,P_NEXT_REVIEW_DATE_WARNING => l_NEXT_REVIEW_DATE_WARNING );
END;
--EMP_LEAVES
--
BEGIN
hr_person_absence_api.create_person_absence
( p_validate => FALSE
,p_effective_date => rec_abs.abs_start_date
,p_person_id => rec_abs.person_id
,p_business_group_id => p_business_group_id
,p_absence_attendance_type_id => rec_abs.absence_attendance_type_id
,p_date_start => rec_abs.abs_start_date
,p_date_end => rec_abs.abs_end_date
,p_absence_days => l_absence_days
,p_absence_hours => l_absence_hours
,p_absence_attendance_id => l_absence_attendance_id
,p_object_version_number => l_object_version_number
,p_occurrence => l_occurrence
,p_dur_dys_less_warning => l_dur_dys_less_warning
,p_dur_hrs_less_warning => l_dur_hrs_less_warning
,p_exceeds_pto_entit_warning => l_exceeds_pto_entit_warning
,p_exceeds_run_total_warning => l_exceeds_run_total_warning
,p_abs_overlap_warning => l_abs_overlap_warning
,p_abs_day_after_warning => l_abs_day_after_warning
,p_dur_overwritten_warning => l_dur_overwritten_warning
);
END;
-- SIT
--
BEGIN
hr_sit_api.create_sit
( p_validate => FALSE
,p_person_id => sit_rec.person_id
,p_business_group_id => p_business_group_id
,p_id_flex_num => l_id_flex_num
,p_effective_date => sit_rec.from_date
,p_date_from => sit_rec.from_date
,p_date_to => sit_rec.to_date
,p_segment1 => sit_rec.type_of_action_code
,p_segment2 => sit_rec.comments
,p_analysis_criteria_id => l_analysis_criteria_id
,p_person_analysis_id => l_person_analysis_id
,p_pea_object_version_number => l_pea_object_version_number );
END;

Oracle HRMS Employee sal proposal &bank details APIs

--sal_proposal
--
BEGIN
hr_maintain_proposal_api.insert_salary_proposal
( p_pay_proposal_id => l_pay_proposal_id
,p_assignment_id => l_assignment_id
,p_business_group_id => p_business_group_id
,p_change_date => sal_rec.start_date
,p_proposed_salary_n => sal_rec.basic
,p_date_to => sal_rec.end_date
,p_proposal_reason => sal_rec.reason_code
,p_object_version_number => l_object_version_number
,p_approved => 'Y'
,p_validate => TRUE
,p_multiple_components => 'N'
,p_element_entry_id => l_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
);
END;
-- EMP bank details
--
BEGIN
hr_personal_pay_method_api.create_personal_pay_method
( p_validate => TRUE
,p_effective_date => l_effective_date --emp_bank_rec.start_date
,p_assignment_id => l_assignment_id
,p_org_payment_method_id => l_org_pay_method_id
,p_priority => 1
,p_percentage => 100
,p_territory_code => 'AE'
,p_segment1 => emp_bank_rec.bank_code
,p_segment2 => emp_bank_rec.bank_code
,p_segment3 => emp_bank_rec.branch_code
,p_segment4 => emp_bank_rec.account
,p_personal_payment_method_id => l_personal_payment_method_id
,p_external_account_id => l_external_account_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_comment_id => l_comment_id
);
END;

Oracle HRMS Employee Medical Assessment & Qualification API

--Create Qualification
--
BEGIN
per_qualifications_api.create_qualification
( p_validate => FALSE
,p_effective_date => TRUNC(sysdate)
,p_qualification_type_id => edu_qual_rec.qual_type_id
,p_business_group_id => p_business_group_id
,p_person_id => edu_qual_rec.person_id
,p_title => edu_qual_rec.title
,p_status => edu_qual_rec.status
,p_awarded_date => edu_qual_rec.awarded_date
,p_start_date => edu_qual_rec.start_date
,p_attendance_id => edu_qual_rec.attendance_id
,p_awarding_body => l_awarding_body
,p_attribute1 => edu_qual_rec.area_of_study
,p_qualification_id => l_qualification_id
,p_object_version_number => l_object_version_number
);
END;
--Create Medical Assessment
--
BEGIN
per_medical_assessment_api.create_medical_assessment
( p_validate => TRUE
,p_effective_date => TRUNC(SYSDATE)
,p_person_id => med_ass_det_rec.person_id
,p_consultation_date => med_ass_det_rec.consultation_date
,p_consultation_type => nvl(med_ass_det_rec.consultation_type,'AM')
,p_consultation_result => med_ass_det_rec.consultation_result
,p_examiner_name => med_ass_det_rec.examiner_name
,p_organization_id => med_ass_det_rec.service_provider_id
,p_next_consultation_date => med_ass_det_rec.next_consultation_date
,p_description => med_ass_det_rec.description
,p_medical_assessment_id => l_medical_assessment_id
,p_object_version_number => l_object_version_number
);
END;

Oracle HRMS Previous Employer & Previous Job APIs

--Create Previous Employer
--
BEGIN
hr_previous_employment_api.create_previous_employer
( p_effective_date => TRUNC(SYSDATE)
,p_validate => FALSE
,p_business_group_id => p_business_group_id
,p_person_id => prev_exp_rec.person_id
,p_start_date => prev_exp_rec.start_date
,p_end_date => prev_exp_rec.end_date
,p_employer_name => prev_exp_rec.employer
,p_employer_country => prev_exp_rec.country_code
,p_employer_type => prev_exp_rec.industry_type_code
,p_all_assignments => 'N'
,p_previous_employer_id => l_previous_employer_id
,p_object_version_number => l_object_version_number
);
END;
--previous job
--
BEGIN
hr_previous_employment_api.create_previous_job
( p_effective_date => TRUNC(SYSDATE)
,p_validate => FALSE
,p_previous_employer_id => l_previous_employer_id
,p_job_name => prev_exp_rec.prev_job
,p_description => prev_exp_rec.PREV_JOB_DESC
,p_previous_job_id => l_previous_job_id
,p_object_version_number => l_pj_object_version_number
);
END;

Oracle HRMS Org Manager & job evaluations APIs

--Create Org Manager
--
BEGIN
hr_organization_api.create_org_manager
( p_validate => TRUE
,p_effective_date => org_mgrs_rec.from_date
,p_organization_id => org_mgrs_rec.org_id
,p_org_info_type_code => 'Organization Name Alias'
,p_org_information1 => org_mgrs_rec.org_designation
,p_org_information2 => org_mgrs_rec.person_id
,p_org_information3 => TO_CHAR(org_mgrs_rec.from_date,'YYYY/MM/DD HH24:MI:SS')
,p_org_information4 => TO_CHAR(org_mgrs_rec.to_date,'YYYY/MM/DD HH24:MI:SS')
,p_org_information_id => l_org_information_id
,p_object_version_number => l_object_version_number
,p_warning => l_warning );
END;
--job_evaluations
--
BEGIN
into per_job_evaluations ( JOB_EVALUATION_ID ,
BUSINESS_GROUP_ID ,
DATE_EVALUATED ,
JOB_ID ,
MEASURED_IN ,
OVERALL_SCORE ,
POSITION_ID ,
SYSTEM ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE ,
COMMENTS )
values
(l_seq_num,
p_business_group_id,
l_date_from,
l_job_id,
'',
to_number(nvl(TRIM(r_job_evaluation.KNOW_HOW_SCORE),0))+
to_number(nvl(TRIM(r_job_evaluation.PROB_SOL_SCORE),0))+
to_number(nvl(TRIM(r_job_evaluation.ACCOUNT_SCORE),0)),
'',
'HAY',
'',
r_job_evaluation.KNOW_HOW_SLOT,
r_job_evaluation.KNOW_HOW_SCORE ,
r_job_evaluation.PROB_SOL_SLOT ,
r_job_evaluation.PROB_SOL_PERC ,
r_job_evaluation.PROB_SOL_SCORE ,
r_job_evaluation.ACCOUNT_SLOT ,
r_job_evaluation.ACCOUNT_SCORE ,
r_job_evaluation.PROFILE ,
sysdate,
p_user_id,
p_last_update_login,
p_user_id,
sysdate,
'');
END;

Oracle HRMS Job Requirement & Hierarchy Element &positions


--Create_Hierarchy_Element
--
BEGIN
hr_hierarchy_element_api.create_hierarchy_element
( p_validate => TRUE
,p_effective_date => TRUNC(SYSDATE)
,p_organization_id_parent => org_hier_rec.parent_org_id
,p_org_structure_version_id => l_org_structure_version_id
,p_organization_id_child => org_hier_rec.child_org_id
,p_business_group_id => p_business_group_id
,p_pos_control_enabled_flag => 'N'
,p_inactive_org_warning => l_inactive_org_warning
,p_org_structure_element_id => l_org_structure_element_id
,p_object_version_number => l_object_version_number
);
END;
--Create_Job_Requirement
--
BEGIN
hr_job_requirement_api.create_job_requirement
( p_validate => TRUE
,p_id_flex_num => l_id_flex_num
,p_job_id => job_req_rec.job_id
,p_segment1 => job_req_rec.qualification_level
,p_segment2 => job_req_rec.discipline
,p_segment3 => job_req_rec.exp_yrs
,p_job_requirement_id => l_job_requirement_id
,p_object_version_number => l_object_version_number
,p_analysis_criteria_id => l_analysis_criteria_id
);
END;
-- positions
--
BEGIN
hr_position_api.create_position
( p_position_id => l_position_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_position_definition_id => l_position_definition_id
,p_name => l_name
,p_object_version_number => l_object_version_number
,p_job_id => position_rec.job_id
,p_organization_id => position_rec.org_id
,p_effective_date => position_rec.from_date
,p_date_effective => position_rec.from_date
,p_validate => TRUE
,p_availability_status_id => 1
,p_business_group_id => p_business_group_id
,p_location_id => position_rec.location_id
,p_max_persons => position_rec.head_count
,p_permanent_temporary_flag => position_rec.on_chart
,p_seasonal_flag => position_rec.off_chart
,p_position_type => 'NONE'
,p_segment1 => position_rec.job_name
,p_segment2 => position_rec.org_name
,p_attribute1 => position_rec.position_name_ar
) ;
END;

Oracle HRMS Grade&JOB APIs

-- Create JOB
--
BEGIN
hr_job_api.create_job
( p_validate => TRUE
,p_business_group_id => p_business_group_id
,p_date_from => job_rec.from_date
,p_job_group_id => l_job_group_id
,p_attribute1 => job_rec.job_craft
,p_attribute2 => job_rec.job_category
,p_attribute3 => job_rec.job_class
,p_attribute4 => job_rec.job_rank
,p_attribute5 => job_rec.job_name_ar
,p_segment1 => job_rec.job_name
,p_job_id => l_job_id
,p_object_version_number => l_object_version_number
,p_job_definition_id => l_job_definition_id
,p_name => l_name
);
END;
-- Update JOB
-- .
BEGIN
hr_job_api.update_job
( p_validate => FALSE
,p_job_id => job_upd_rec.job_id
,p_object_version_number => l_object_version_number
,p_job_definition_id => l_job_definition_id
,p_attribute5 => job_upd_rec.job_name_ar
,p_name => l_name
,p_valid_grades_changed_warning => l_valid_grades_changed_warning
,p_effective_date => l_effective_date
);
END;
--Create valid Grade
--
BEGIN
hr_valid_grade_api.create_valid_grade
( p_validate => FALSE
,p_grade_id => val_grade_rec.grade_id
,p_date_from => '01-JAN-1971'
,p_effective_date => '01-JAN-1971'
,p_job_id => val_grade_rec.job_id
,p_valid_grade_id => l_valid_grade_id
,p_object_version_number => l_object_version_number
);
END;

Oracle HRMS Organization API

--ORGANIZATION
--
BEGIN
hr_organization_api.create_hr_organization
( p_validate => l_validate
,p_effective_date => l_effective_date
,p_business_group_id => l_business_group_id
,p_name => l_name
,p_date_from => l_date_from
,p_location_id => l_location_id
,p_internal_external_flag => l_internal_external_flag
,p_type => l_type
,p_enabled_flag => 'Y'
,p_object_version_number_inf => l_object_version_number_inf
,p_object_version_number_org => l_object_version_number_org
,p_organization_id => l_organization_id
,p_org_information_id => l_org_information_id
,p_duplicate_org_warning => l_duplicate_org_warning
);
END

Miscellaneous receipts API oracle

l_return_status VARCHAR2(10);
l_global_attribute_rec ar_receipt_api_pub.global_attribute_rec_type
DEFAULT ar_receipt_api_pub.global_attribute_rec_const;
l_issuer_name VARCHAR2 (75) DEFAULT NULL;
l_issue_date DATE DEFAULT NULL;
l_issuer_bank_branch_id NUMBER DEFAULT NULL;
l_attribute_rec ar_receipt_api_pub.attribute_rec_type
DEFAULT ar_receipt_api_pub.attribute_rec_const;
l_msg_count NUMBER DEFAULT NULL;
l_msg_index_out NUMBER;
l_msg_data1 VARCHAR2 (2000) DEFAULT NULL;
l_msg_count1 NUMBER DEFAULT NULL;
l_msg_index_out1 NUMBER;
v_receipt_id NUMBER;
l_commit VARCHAR2 (15) := fnd_api.g_false;
l_msg_data VARCHAR2 (2000) DEFAULT NULL;
l_cr_id NUMBER;
l_receipt_number VARCHAR2 (15);
BEGIN
l_receipt_number :='ADI';
begin
ar_receipt_api_pub.create_misc
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => 'GBP',
p_amount => 500,
p_receipt_date => SYSDATE,
p_gl_date => SYSDATE,
p_receipt_method_id => 2496,
p_activity => 'Miscellaneous Cash',
p_misc_receipt_id => l_cr_id ,
p_receipt_number => l_receipt_number,
p_receivables_trx_id => 2219
);
EXCEPTION WHEN OTHERS
THEN
fnd_file.put_line(fnd_file.log,'RECEIPT API FAILED @ WHEN OTHERS - 'substr(SQLERRM,1,90));
END;
fnd_file.put_line(fnd_file.log,'l_return_status 'l_return_status);
fnd_file.put_line(fnd_file.log,'RECEIPT ID 'l_cr_id);
IF l_cr_id IS NULL THEN
FOR j IN 1 .. fnd_msg_pub.count_msg
LOOP
BEGIN
fnd_msg_pub.get (p_msg_index => j,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out
);
l_msg_data := l_msg_data '-' SUBSTR(l_msg_data,1,2000);
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END LOOP;
fnd_file.put_line(fnd_file.log,'l_msg_data'l_msg_data);
END IF;


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


Derive the values from below Quries


SELECT NAME FROM AR_RECEIVABLES_TRX_ALL WHERE RECEIVABLES_TRX_ID=2219


---Receivable Activity p_receivables_trx_id


SELECT NAME FROM AR_RECEIPT_METHODS WHERE RECEIPT_METHOD_ID=2496


---Receipt Method p_receipt_method_id


Oracle Attchments API

CREATE OR REPLACE PROCEDURE XX_ATTACH_LOAD (p_short_text IN Varchar2,
p_cat_id IN Number,
p_pk1_val IN Number,
p_entity_name IN Varchar2,
x_return_status OUT VARCHAR2,
x_return_msg OUT VARCHAR2
) AUTHID CURRENT_USER
/******************************************************************************/
IS
ln_attached_doc_id NUMBER;
lv_language Varchar2(10);
ln_media_id NUMBER;
ln_row_id Varchar2(100);
ln_doc_id NUMBER;
ln_user_id apps.FND_USER.USER_ID%TYPE;
BEGIN
/***************************************************************
To get the attached document seq id
****************************************************************/
SELECT FND_ATTACHED_DOCUMENTS_S.nextval
INTO ln_attached_doc_id
FROM dual;
SELECT USERENV('LANG')
INTO lv_language
FROM dual;
SELECT user_id
INTO ln_user_id
FROM fnd_user
WHERE UPPER (user_name) = 'FKI_INT';--'OMADM-920';
/***************************************************************
To get the Short Text media id
****************************************************************/
SELECT fnd_documents_short_text_s.nextval
INTO ln_media_id
FROM dual;
ln_row_id := NULL;
ln_doc_id := NULL;
/***************************************************************
Thiis procedure call will create the records in
a) fnd_documents_tl
b) fnd_documents
c) fnd_attached_documents
****************************************************************/
apps.fnd_attached_documents_pkg.insert_row(
X_rowid => ln_row_id,
X_attached_document_id => ln_attached_doc_id,
X_document_id => ln_doc_id,
X_creation_date => SYSDATE,
X_created_by => ln_user_id,
X_last_update_date => SYSDATE,
X_last_updated_by => ln_user_id,
X_last_update_login => -1,--ln_user_id,
X_seq_num => 10,
X_entity_name => p_entity_name, --'GMD_SAMPLES',
X_column1 => NULL,
X_pk1_value => p_pk1_val,
X_pk2_value => NULL,
X_pk3_value => NULL,
X_pk4_value => NULL,
X_pk5_value => NULL,
X_automatically_added_flag => 'N',
X_request_id => NULL,
X_program_application_id => NULL,
X_program_id => NULL,
X_program_update_date => NULL,
X_attribute_category => NULL,
X_attribute1 => NULL,
X_attribute2 => NULL,
X_attribute3 => NULL,
X_attribute4 => NULL,
X_attribute5 => NULL,
X_attribute6 => NULL,
X_attribute7 => NULL,
X_attribute8 => NULL,
X_attribute9 => NULL,
X_attribute10 => NULL,
X_attribute11 => NULL,
X_attribute12 => NULL,
X_attribute13 => NULL,
X_attribute14 => NULL,
X_attribute15 => NULL,
X_datatype_id => 1,
X_category_id => p_cat_id,
X_security_type => 4,
X_security_id => NULL,
X_publish_flag => 'Y',
X_image_type => NULL,
X_storage_type => NULL,
X_usage_type => 'O',
X_language => lv_language,
X_description => NULL,
X_file_name => NULL,
X_media_id => ln_media_id,
X_doc_attribute_category => NULL,
X_doc_attribute1 => NULL,
X_doc_attribute2 => NULL,
X_doc_attribute3 => NULL,
X_doc_attribute4 => NULL,
X_doc_attribute5 => NULL,
X_doc_attribute6 => NULL,
X_doc_attribute7 => NULL,
X_doc_attribute8 => NULL,
X_doc_attribute9 => NULL,
X_doc_attribute10 => NULL,
X_doc_attribute11 => NULL,
X_doc_attribute12 => NULL,
X_doc_attribute13 => NULL,
X_doc_attribute14 => NULL,
X_doc_attribute15 => NULL );
/***************************************************************
This inserts the related text as an attachment in short text
table
***************************************************************/
INSERT INTO
FND_DOCUMENTS_SHORT_TEXT( MEDIA_ID,
SHORT_TEXT)
VALUES ( ln_media_id,
p_short_text);
COMMIT;
x_return_status := 'S';
EXCEPTION
WHEN OTHERS THEN
x_return_status := 'E';
x_return_msg := 'Exception Occured : 'SQLCODE'-'SQLERRM;
END XX_ATTACH_LOAD;

Oracle order management tables

Entered
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
Booked
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release
Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock
Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned
Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock

Ship Confirmed
wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped
oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N

Autoinvoice
wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program for
ra_customer_trx_lines_all specific batch transaction tables get populated
Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines
Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.
Payment Terms
ra_terms Payment terms
AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.
Customer Information
hz_parties Get Customer information include name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers
Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments
Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes
End User Details
csi_t_party_details To capture End user Details
Sales Credit Sales Credit Information(How much credit can get)
oe_sales_credits
Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text
Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all
Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions
Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all
Hold Relaese
oe_hold_releases_all Hold released Sales Order.
Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.
Cancel Orders
oe_order_lines_all Cancel Order Details.

Oracle Drop Shipments












Overview
1.1 Business Requirement
Drop shipments occur when your customer orders product from you, you order the product from your supplier, and your supplier ships the product directly to your customer.
Drop shipping functionality enables you to take an order from your customer and fulfill it directly from your supplier's site. You can receive orders for items that you do not stock or for which you lack sufficient inventory, and have a supplier provide the items directly to your customer.

The benefits of drop shipping include:

■ No inventory is required
■ Reduced order fulfillment processing costs
■ Reduced flow times
■ Elimination of losses on non-sellable goods
■ Elimination of packing and shipping costs
■ Reduced inventory space requirements
■ Reduced shipping time to your customer
■ Enables you to offer a variety of products to your customers

1.2 Oracle Features
Oracle Order Management enables you to enter drop ship sales orders and lines for standard, model and kit, and configured items, although you currently cannot perform a drop shipment for Ship Model Complete (SMC) PTO’s. Order Management integrates with Purchasing to fulfill drop-ship orders. It populates the PO requisitions interface table with information for order lines that need to be fulfilled via an external source. Purchasing calls Order Management APIs (OE_DROP_SHIP_GRP) to communicate information about the requisition, purchase order and receipts. Order Management provides a link to the Drop Ship information from the Additional Line and Additional Order Information windows of the Sales Order Form
Application Setup






The following Application Setups are required for the Drop Shipment Business Flow.
ITEM ATTRIBUTES

Inventory -> Items - > Master items

Purchasing:
Purchased > Enabled Purchasable > Enabled
List Price (must be entered)











Inventory:
Transactable > Enabled Stockable > Optional
Reservable > Optional Inventory Item > Optional











Order Management:
Customer Ordered > Enabled Customer Orders Enabled > Enabled
Internal Ordered > Disabled Internal Orders Enabled > Disabled
Shippable > Optional OE Transactable > Enabled








Receiving: Receipt Routing set to Direct










All Drop Ship items (and all external ATO or PTO models, it's option class and options) must be defined in the organization entered in the profile option OE: Item Validation Organization and in the Receiving Organization.

NOTE:
Drop-ship items must be received in a logical (dummy) organization. If using Oracle master Scheduling/MRP and Oracle Supply Chain Planning avoid miscounting supply by not including these logical organizations in the planning.



TRANSACTION TYPE and SOURCE TYPE

To define a transaction type for your drop ship orders, and have the source type default to External on all sales order lines, the following setups must be done:

A. TRANSACTION TYPE: Orders/Returns ->Setup -> Transaction Types -> Define















The Line Flow - Generic workflow will handle drop ship lines. It calls the 'Create Supply - Line' process. This process has a function called 'Branch on Source Type' which will detect an item with a Source Type of 'External’ and set the line to a 'Purchase Release - Deferred' status. Then when the Workflow Background processor picks up the line, it will initiate the Purchase Release process to write the records to the PO_REQUISITIONS_INTERFACE table. When Requisition import program is run the records will be pulled into Purchasing to create the requisition.



B. SOURCE TYPE DEFAULTING
Under the Shipping Tab in Sales Order form, the Shipping Source Type should be 'External'.
Orders/Returns -> Setup -> Rules -> Defaulting
Query up Entity = Order Line; Click on Attribute = Source Type, Click on Defaulting Source Rules





Add a new record in Defaulting Conditions by clicking on the '+' button in the toolbar
Select Defaulting Condition = ‘Regular Line’. Give it a precedence value of 5

Add a new record in Default Sourcing Rules by clicking on the '+' button in the toolbar
Select Source Type = Related Record

The Default Source/Value flex field window opens
Click on the List of Values for Related Object > Line Type will default
Click on the List of Values for Related Attribute > Source Type will default

Save the record











GL Accounts Highrarcy Query R12

SELECT DISTINCT
ALEVEL.PARENT_FLEX_VALUE Alevel,
(SELECT DESCRIPTION
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE=ALEVEL.PARENT_FLEX_VALUE
AND FLEX_VALUE_SET_ID=1014368) ALEVELDESCRIPTION,
BLEVEL.PARENT_FLEX_VALUE Blevel,
(SELECT DESCRIPTION
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE=BLEVEL.PARENT_FLEX_VALUE
AND FLEX_VALUE_SET_ID=1014368) BLEVELDESCRIPTION,
CLEVEL.PARENT_FLEX_VALUE Clevel,
(SELECT DESCRIPTION
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE=CLEVEL.PARENT_FLEX_VALUE
AND FLEX_VALUE_SET_ID=1014368) CLEVELDESCRIPTION,
DLEVEL.PARENT_FLEX_VALUE Dlevel,
(SELECT DESCRIPTION
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE=DLEVEL.PARENT_FLEX_VALUE
AND FLEX_VALUE_SET_ID=1014368) DlevelDescription,
glbss.PERIOD_NAME PERIOD,
GLCC.SEGMENT2 Costcenter,
ffv2.description CostcenterDescription,
GLCC.SEGMENT3 Subjective,
ffv.description SubjectiveDescription,
GLCC.SEGMENT4 Analysis,
ffv1.Description AnalysisDescription,
(SELECT nvl(SUM(GL_BALANCES. PERIOD_NET_DR-GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS
WHERE ACTUAL_FLAG ='A'
AND PERIOD_YEAR =glbss.PERIOD_YEAR-1
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=glcc.CODE_COMBINATION_ID) PriorYearActual,
(SELECT nvl(SUM(GL_BALANCES. PERIOD_NET_DR-GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS
WHERE ACTUAL_FLAG ='A'
AND PERIOD_NAME =glbss.PERIOD_NAME
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=glcc.CODE_COMBINATION_ID)ThisPeriodActual,
(SELECT nvl(SUM(GL_BALANCES.BEGIN_BALANCE_DR
+GL_BALANCES.PERIOD_NET_DR-GL_BALANCES.BEGIN_BALANCE_CR-
GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS,GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='B'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND PERIOD_NAME =glbss.PERIOD_NAME
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=glcc.CODE_COMBINATION_ID)YTDCurrentBudget,
(SELECT nvl(SUM(GL_BALANCES.BEGIN_BALANCE_DR
+GL_BALANCES.PERIOD_NET_DR-GL_BALANCES.BEGIN_BALANCE_CR-
GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS
WHERE ACTUAL_FLAG ='A'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND PERIOD_NAME =glbss.PERIOD_NAME
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=glcc.CODE_COMBINATION_ID) YTDActual,
((SELECT nvl(SUM(GL_BALANCES.BEGIN_BALANCE_DR
+GL_BALANCES.PERIOD_NET_DR-GL_BALANCES.BEGIN_BALANCE_CR-
GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS
WHERE ACTUAL_FLAG ='A'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND PERIOD_NAME =glbss.PERIOD_NAME
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=glcc.CODE_COMBINATION_ID) -
(SELECT nvl(SUM(GL_BALANCES.BEGIN_BALANCE_DR
+GL_BALANCES.PERIOD_NET_DR-GL_BALANCES.BEGIN_BALANCE_CR-
GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS,GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='B'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND PERIOD_NAME =glbss.PERIOD_NAME
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=glcc.CODE_COMBINATION_ID)) YTDVariance,
(SELECT nvl(SUM(GL_BALANCES. PERIOD_NET_DR-GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS ,
GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='B'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=glcc.CODE_COMBINATION_ID)FullYearBudget,
(SELECT nvl(SUM(GL_BALANCES. PERIOD_NET_DR-GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS
WHERE ACTUAL_FLAG ='A'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID) FULLYEARACTUAL,
(SELECT nvl(SUM(GL_BALANCES. PERIOD_NET_DR-GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS ,
GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='E'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID)FULLYEARENCUMBR,
(((SELECT nvl(SUM(GL_BALANCES. PERIOD_NET_DR-GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS
WHERE ACTUAL_FLAG ='A'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID))
+
((SELECT nvl(SUM(GL_BALANCES. PERIOD_NET_DR-GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS ,
GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='E'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID)))FULLYEARTOTAL,
((SELECT nvl(SUM(GL_BALANCES. PERIOD_NET_DR-GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS ,
GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='B'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID)
-
((SELECT nvl(SUM(GL_BALANCES. PERIOD_NET_DR-GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS
WHERE ACTUAL_FLAG ='A'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID))
+
((SELECT nvl(SUM(GL_BALANCES. PERIOD_NET_DR-GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS ,
GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='E'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_CODE_COMBINATIONS.SEGMENT2 =GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID)))FULLYEAREMAINNG,
(SELECT nvl(SUM(GL_BALANCES.BEGIN_BALANCE_DR
+GL_BALANCES.PERIOD_NET_DR-GL_BALANCES.BEGIN_BALANCE_CR-
GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS,GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='B'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND PERIOD_NAME =glbss.PERIOD_NAME
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID)FORECASTBUDGET,
(SELECT nvl(SUM(GL_BALANCES.BEGIN_BALANCE_DR
+GL_BALANCES.PERIOD_NET_DR-GL_BALANCES.BEGIN_BALANCE_CR-
GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS,GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='B'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND PERIOD_NAME =glbss.PERIOD_NAME
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC FORECAST'
AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID)FORECAST,
((SELECT nvl(SUM(GL_BALANCES.BEGIN_BALANCE_DR
+GL_BALANCES.PERIOD_NET_DR-GL_BALANCES.BEGIN_BALANCE_CR-
GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS,GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='B'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND PERIOD_NAME =glbss.PERIOD_NAME
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID)
-
(SELECT nvl(SUM(GL_BALANCES.BEGIN_BALANCE_DR
+GL_BALANCES.PERIOD_NET_DR-GL_BALANCES.BEGIN_BALANCE_CR-
GL_BALANCES.PERIOD_NET_CR),0)
FROM GL_BALANCES ,
GL_CODE_COMBINATIONS,GL_BUDGET_VERSIONS
WHERE ACTUAL_FLAG ='B'
AND PERIOD_YEAR =glbss.PERIOD_YEAR
AND PERIOD_NAME =glbss.PERIOD_NAME
AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID= GL_BALANCES.CODE_COMBINATION_ID
AND GL_BALANCES.BUDGET_VERSION_ID = GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC FORECAST'
AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
AND GL_BALANCES.CODE_COMBINATION_ID=GLCC.CODE_COMBINATION_ID))FORECASTVARIANCE
from
GL_CODE_COMBINATIONS GLCC,
GL_BALANCES glbss,
FND_FLEX_VALUES_vl FFV,
FND_FLEX_VALUES_VL FFV1,
FND_FLEX_VALUES_VL FFV2,
(select *
from fnd_flex_value_norm_hierarchy a, fnd_flex_values_vl b
WHERE A.FLEX_VALUE_SET_ID=1014368
AND A.FLEX_VALUE_SET_ID=B.FLEX_VALUE_SET_ID
AND parent_flex_value LIKE 'D%'
AND B.FLEX_VALUE BETWEEN A.CHILD_FLEX_VALUE_LOW AND A.CHILD_FLEX_VALUE_HIGH) DLEVEL,
(select *
from fnd_flex_value_norm_hierarchy a, fnd_flex_values_vl b
WHERE A.FLEX_VALUE_SET_ID=1014368
AND A.FLEX_VALUE_SET_ID=B.FLEX_VALUE_SET_ID
AND PARENT_FLEX_VALUE LIKE 'C%'
and b.flex_value BETWEEN a.child_flex_value_low AND a.child_flex_value_high) Clevel,
(select *
from fnd_flex_value_norm_hierarchy a, fnd_flex_values_vl b
WHERE A.FLEX_VALUE_SET_ID=1014368
AND A.FLEX_VALUE_SET_ID=B.FLEX_VALUE_SET_ID
AND PARENT_FLEX_VALUE LIKE 'B%'
AND B.FLEX_VALUE BETWEEN A.CHILD_FLEX_VALUE_LOW AND A.CHILD_FLEX_VALUE_HIGH) BLEVEL,
(select *
from fnd_flex_value_norm_hierarchy a, fnd_flex_values_vl b
WHERE A.FLEX_VALUE_SET_ID=1014368
AND A.FLEX_VALUE_SET_ID=B.FLEX_VALUE_SET_ID
AND PARENT_FLEX_VALUE LIKE 'A%'
and b.flex_value BETWEEN a.child_flex_value_low AND a.child_flex_value_high) Alevel
WHERE glcc.segment3= ffv.flex_value
and glcc.segment4= ffv1.flex_value
AND GLCC.SEGMENT2= FFV2.FLEX_VALUE
AND DLEVEL.FLEX_VALUE=GLCC.SEGMENT2
AND CLEVEL.FLEX_VALUE=DLEVEL.PARENT_FLEX_VALUE
AND BLEVEL.FLEX_VALUE=CLEVEL.PARENT_FLEX_VALUE
and Alevel.flex_value=BLEVEL.parent_flex_value
and glcc.CODE_COMBINATION_ID=glbss.CODE_COMBINATION_ID
and ffv.flex_value_set_id=1014369
and ffv1.flex_value_set_id=1014370
AND FFV2.FLEX_VALUE_SET_ID=1014368
and ACTUAL_FLAG in('A','B','E')
ORDER BY 1,2